CODEWITHSUNDEEP
powerbidax
alexrai93
alexrai93

Reputation: 643

Why does DATESMTD return wrong values without RELATEDTABLE when slicing by date column?

I have a simple test model with two tables related by Date but when I try use the date table as a discrete dimension with a DATESMTD measure it returns the wrong results. Specifically, when I select Day-of-week (Mon, Tues, Weds...) as my x axis on a bar chart.

This is the test model I set up

SalesTable *–1 DateTable

This Measure below Works - The sum of the daily bars equals the MTD Sales number.

Sales MTD := CALCULATE(SUM(SalesTable[Sales]), RELATEDTABLE(DateTable), DATESMTD(DateTable[TheDate]))

This measure below does not work - Monday contains the sum of the entire MTD Sales number, and each bar gets progressively smaller but the grand total of all bars is much greater than the MTD number.

Sales MTD := CALCULATE(SUM(SalesTable[Sales]), DATESMTD(DateTable[TheDate]))

I was able to resolve this by using RELATEDTABLE(DimDate) or it's alias CALCULATETABLE(DimDate).

It's forcing a context transition but does anyone know why this step is necessary or why it works? I don't need this function for say running DATESMTD calculations on a customer dimension, but I do with the date dimension.

My understanding was that if you had a one-to-many relationship PowerBI would handle the details and it should calculate the sum of sales MTD for each day-of-week.

Upvotes: 0

Views: 319

Answers (1)

Marcus
Marcus

Reputation: 4005

My suggestion is: Is your date table marked as one?

When using time intelligence on date tables, the (necessary) REMOVEFILTERS ( 'Date' ) filter in DAX is added automatically by the engine (this removes any existing filters on any columns in your date table). I think this may be causing your issues. In your above code, REMOVEFILTERS does the same as CALCULATETABLE and RELATEDTABLE (in this specific case) - they will all just give you the entire date table as a filter into your code, thereby removing any outside filters on date.

To mark as date table, right click your table and find the menu option as below:

enter image description here

Upvotes: 0

Related Questions