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.

  • Shuwi

    Very nice and informative screencast Kasper ! Thank you 🙂

  • Great examples Kasper!

  • John

    FINALLY!! Some real live examples. THANK YOU! There’s only so much syntax I can handle trying to figure out DAX / Measures.

  • greg kramer

    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.

  • Kasper de Jonge

    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

  • Brad

    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.

  • Kasper de Jonge

    @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.

  • Simon Taylor

    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

    @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

    @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

  • Kasper de Jonge

    @Simon Taylor great 🙂 If you have more questions let me know 🙂

  • khaoula

    hi kasper,
    i’m working on project using powerpivot , and after calculating KPI, i get used to show every KPI the previous last 12 weeks from a variable date selected by the user on dynamic dashbored(specifecly a charts)

  • khaoula

    hi kasper
    for more details about my problem, i have difficulties to get the right formula with powerpivot -DAX to get the last 12 weeks from a variable date, i want to have in a calculated colomn not in measure in the charts.
    i have to get charts which shows the KPI during the last 12 weeks.
    i m using the time intelligence function but every time , i dont found the right formula, please help me