Wednesday, May 3, 2017

Power BI - DAX TOTALYTD does not calculate the values properly

There are multiple ways of calculating YTD values using DAX function and one of the easiest ways of calculating it is using TOTALYTD function. This needs some specific settings and once everything is set, it works as we expected. However, I noticed an issue, it may not be an issue but it is something to remember when using this function.

Let me explain what I did first. I connected with AdventureWorksDW2014 database and imported two tables: DimDate and FactInternetSales. Since the DimDate dimension is a Role-Playing dimension, it shows three relationships between these two tables. I removed two and kept only the link between DateKey and OrderDateKey.


This is how the relationship has been set.


Once it is done, I created a new measure called SalesAmountYTD as below;

SalesAmountYTD = TOTALYTD(SUM(FactInternetSales[SalesAmount]), DimDate[FullDateAlternateKey])

In order to see the YTD values for months of 2013, I created a Column Chart and set EnglishMonthName of DimDate (sorted by MonthNumber) as the X Axis. Then both SalesAmount and SalesAmountYTD are added as values and used CalenderYear for filtering the entire page for 2013. This is what I see when it is viewed;


As you see, YTD values are not getting calculated accurately. I did many searches and applied many solutions and nothing worked. But finally I figured it out, it is all about the columns I have used for linking both tables. The original relationship between these two tables is based on DateKey and OrderDateKey. When I change the columns for defining the relationship as FullDateAlternateKey and OrderDate as below, it started working.


Here is the output.


I am not sure the exact issue or theory related, have already asked some experts, once received the feedback, will share it. This must be a small thing that I do not see, if you know it, please share it :).


2 comments:

Exploring Data said...

Looking forward for the solution.

I too have tried the same while reading this blog post not able to figure out why it is happening ?

Dinesh Priyankara said...

Hi,

I think that it needs a relationship using datetime values. Anyway, I have asked some experts, will update this if I get the answer.