BISM – Period To Date Calcs


DAX has three periods to date functions which easily allow the creation of common the common measures (Month To Date, Quarter To Date and Year To Day). This post looks at period to date calculations and member sorting in the BISM tabular model.

Sorting

By default the tabular model sorts members by name. For example, clicking on the month field shows the alphabetical listing of months. Although these values are shown in the project model they are displayed in the same order when the project is deployed.The order of these members can be changed and sorted by an additional column by using the sort by column function (menu path: Column > Sort > Sort By Column).When this is done, we simply specify the sort column in the popup (below).

After sorting, members appear as expected in both the project design and deployed project.

Period to Date Calculations

Period to date can be easily created using the DAX functions TOTALYTD, TOTALMTD, TOTALQTD. Note that these functions are not dependent on sort order but on a related date field.

For example, a date field in the related DimTime table.Note that there is a primary relationship between FactResellerSales and DimTime and the inclusion of the TimeDate (which is a date data type) column in DimTime.

The general format of the function is TOTALPPP ( scalar_function , time_column) where the time_column is a primary related date field. To show YTD Values for SalesAmount we use the formula:

Sales YTD:=TOTALYTD(sum(FactResellerSales[SalesAmount]), DimTime[TimeDate])

Also note that measures (as they would be thought of in SSAS) must be added to the ‘Measure Grid’. If they are not, measures are not seen in the cube. The table FactResellerSales has columns hidden from client tools (so that no dimension members are created) and contains the measures [Sales] and [Sales YTD] (below).

Browsing the cube shows data (sorted) with YTD values as expected. Unlike SSAS, the tabular model will show the [YTD] measure defaulting to the last year when the (related) time table is not used.


MISC

The technet reference for these functions can be found at YTD , QTD , MTD .

Advertisements

7 thoughts on “BISM – Period To Date Calcs

  1. In Europa we have some standards…
    The week follows ISO 8601, means the last week of the year can follow into next year (a year can ex. start with week number 53)
    In excel we can use the parameter 21 to use the ISO standard, but not in Power Pivot.
    Is it possible to do something around it in power pivot?

  2. No…
    To have a whole week as one number!
    Ex. in the end of 2009 and beginning of 2010 (week number 53 from monday 28. December 2009 – sunday 3. January 2010)…

  3. Pingback: DAX : YTD on 445 Calender and Irregular End Dates « Paul te Braak

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s