Thomas.I Microsoft BI & Analytics

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

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

Advertisements

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

 
%d bloggers like this: