ProductX returning unexpected results when iterating over date.

[MKeppel]

I have created two PBIX file, everything is the same, except for the date dimention is useding a string, 1-8, instead of a date.

With that being the only change, I am getting getting different results on productx measure  

Link to the two PBIX Files

https://docs.mobius.kiwi/f/4526097a6d2e49cdbd5c/ 

https://docs.mobius.kiwi/f/3066c5de5db04c9fbd79/

The measure I am using is

tmpmreturnsproduct =

var mindateapplied = MINX(ALLSELECTED('DimDate'), 'DimDate'[Date])

return

100 * CALCULATE(PRODUCTX('DimDate',1+[tmpmReturns]),filter(all('DimDate'),'DimDate'[Date]<=max('DimDate'[Date]) && 'DimDate'[Date] >= mindateapplied))

I am completly stumpt has to why I would get different results with iterating over a date, or have had any luck working out the correct Dax for the Date formated version.

Doing Some more testing. As soon as I change the Datatype in the Date Table from Whole Number to Date, the 'calculation error' occuers...

[lbendlin · Accepted solution]

get rid of the Auto Date/Time hierarchy.

[MKeppel]

That does look like it works on the file I sent over.

I just tried it on my main file, I still have the same issue.. Any ideas?

[lbendlin]

Disable auto date/time globally.

[MKeppel]

Takes, forgot ther is one for global and one for dataset. 

However, playing around with it more (making it work with my main dataset, i was still having isssues), for some strange unknown reason, on the date table as soon as I create Day Number Column 

Day Number = DAY(DimDate[Date])

even just creating another column with the same value Date2 = DimDate[Date] causes the error, but some fomulars like EOM = EOMONTH(DimDate[Date],0)  works fine?

 Surely this is some type of bug now?

[lbendlin]

Can you provide a pbix that demonstrates the issue?

[MKeppel]

https://docs.mobius.kiwi/f/25c11a74ec9c4891bbe9/

Look at the tmpmreturnsproduct , returning all 100's.. then delete the column day number column and the measure calculates as you would expect.

[lbendlin]

I did some mild refactoring.  You will want to avoid nested measures if you can, and fancy filter constructs.

I seem to recall that the Italians have already written about your scenario. Something about a covering column that needs to be included in the filter context.

[MKeppel]

Thanks for that, time go back to reading the fundementals....

[lbendlin]

Here's an interesting article around the subject

Introducing ALLSELECTED in DAX - SQLBI

[MKeppel]

Thanks... Would love to know what / why that causes the problem.