Screencast: Introduction to time intelligent functions in PowerPivot
One of the great features of PowerPivot is DAX, especially the time intelligent functions are great. This video gives a introduction on starting with the time intelligent functions in DAX:
For a complete list of DAX time intelligent functions check the site of Vidas Matelis. One of the main DAX functions used is the Calculate function, Marco Russo created a excellent post on this function.
Very nice and informative screencast Kasper ! Thank you
Great examples Kasper!
FINALLY!! Some real live examples. THANK YOU! There’s only so much syntax I can handle trying to figure out DAX / Measures.
Starting the global Kasper for PowerPivot President campaign
Kasper – great example and tremendously helpful to see it worked. Thanks
HELP – Can you please clarify that slicers applied to an example like this will not be applied because of the need to use “ALL(table)” in the CALCULATE function. This is what I am finding in my example.
Hi @Frank ,
I know what you mean, this is indeed true when have your facts and dates in the same table, The ALL overwrites the slicer selection.
What you can do is copy your date values to a new excel sheet, use the remove duplicate function and load it to PowerPivot by using linked tables. Create a key and a relationship. Then use the date column from the new table in your timeintelligent functions and use ALL(DATETABLE) instead of all(facttable). This should work, i did it myself recently. I’m figuring out how we can do it without resorting to this workaround.
Kasper
Kasper,
Great article and great blog.
Do these functions work when the financial year differs from the Calendar year. Here in Australia her have a Jul-Jun Financial year we are always doing YTD type calculations.
Thanks.
@Brad
Hi Brad, i created a blog post on how to do this: http://business-intelligence.kdejonge.net/using-time-intelligent-powerpivot-functions-with-fiscal-year
Hope this solves your problem.
I am using your example calculate functions to try and create measure but am having some problems.
I can create a measure which shows me the total for the previous month.
I have a pivot table which shows me expense per month, per master category and sub category.
The measure when applied to the pivot shows me the total value per month on each of the master category and sub category rows.
I have been looking into how to apply a filter to the calculate function whereby it filters the response in the context of the pivot table master and sub category rows but i cant quite grasp it. Do you have any ideas.
Thanks
Simon
@Simon Taylor
This is my attempt to obtain the last months data for the same element in the pivot table:-
=CALCULATE(SUM(SourceData[Out]),PARALLELPERIOD(SourceData[Date],-1,MONTH),
SourceData[Recurring] = IF(COUNTROWS(VALUES(SourceData[Recurring])) = 1,
VALUES(SourceData[Recurring])
),
SourceData[Master Category] = IF(COUNTROWS(VALUES(SourceData[Master Category])) = 1,
VALUES(SourceData[Master Category])
),
SourceData[CATEGORY] = IF(COUNTROWS(VALUES(SourceData[CATEGORY])) = 1,
VALUES(SourceData[CATEGORY])
)
)
@Simon Taylor
In the end it worked out to be quite simple (isnt it always):-
=CALCULATE( [Sum of Out] , DATEADD(DimDate[DateKey], -1, MONTH))
And then :-
=[Sum of Out] – [Sum PM]
Your screencast got me started though!
Cheers
@Simon Taylor great
If you have more questions let me know 