Thomas.I Microsoft BI & Analytics

A Site Dedicated to General BI Issues and MS BI Issues: Architecture and Technology

Posts Tagged ‘DAX Time Intellignce;MDX Time Intelligence’

DAX Code Snippet: Year To Date in Charts

Posted by thomasivarssonmalmo on June 3, 2015

This short blog post tries to show the different ways to solve the same problem in DAX and in MDX.

If you look at the picture you can see two ways of presenting a year to date value. The last picture continues with the last value for all empty months. The example is from measuring database file sizes over time. The charts are made in Power BI Designer.

 

Two diagrams with accumulated values

The last chart is simple to create in DAX:

TotalYTDSize = TOTALYTD(SUM(‘DW AssesmentDatabaseFileSizes'[size]),’DW DimDateExt'[DateID])

What about the first chart? I had to do a search and found the result in the MSDN new group about DAX but not in DAX books.

TotalYTDSize2 = IF(ISBLANK(SUMX(‘DW AssesmentDatabaseFileSizes’,’DW AssesmentDatabaseFileSizes'[size])),BLANK(),TOTALYTD(SUM(‘DW AssesmentDatabaseFileSizes'[size]),’DW DimDateExt'[DateID]))

The interesting part is that you need a DAX table function, SUMX, because SUM will not work.

What about the same problem in MDX? I am using the know Adventure Works cube instead and show the result in a grid.

With Member Measures.YTDOrderQty AS
SUM(YTD([Date].[Calendar]),[Measures].[Internet Order Quantity])

Member  Measures.YTDOrderQtyAdj AS
IIF(ISEMPTY([Measures].[Internet Order Quantity]),NULL,SUM(YTD([Date].[Calendar]),[Measures].[Internet Order Quantity]))

Select {[Measures].[Internet Order Quantity],Measures.YTDOrderQty,Measures.YTDOrderQtyAdj} On 0,
Descendants([Date].[Calendar],[Date].[Calendar].[Month]) on 1
FROM [Adventure Works]

And here is the result in the grid. It is the last column that works like the first chart above.

MDX Result

Posted in DAX Time Calculations, MDX for everyone | Tagged: | Leave a Comment »