topic Re: ProductX returning unexpected results when iterating over date. in DAX Commands and Tips

ProductX returning unexpected results when iterating over date.

MKeppel — Tue, 13 Jun 2023 23:01:06 GMT

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...

Re: ProductX returning unexpected results when iterating over date.

lbendlin — Thu, 15 Jun 2023 01:15:44 GMT

get rid of the Auto Date/Time hierarchy.

Re: ProductX returning unexpected results when iterating over date.

MKeppel — Thu, 15 Jun 2023 01:43:24 GMT

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

Re: ProductX returning unexpected results when iterating over date.

MKeppel — Thu, 15 Jun 2023 02:05:46 GMT

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?

Re: ProductX returning unexpected results when iterating over date.

lbendlin — Thu, 15 Jun 2023 10:58:19 GMT

Disable auto date/time globally.

Re: ProductX returning unexpected results when iterating over date.

MKeppel — Wed, 21 Jun 2023 01:33:32 GMT

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?

Re: ProductX returning unexpected results when iterating over date.

lbendlin — Sun, 09 Jul 2023 15:08:35 GMT

Can you provide a pbix that demonstrates the issue?

Re: ProductX returning unexpected results when iterating over date.

MKeppel — Mon, 10 Jul 2023 01:18:26 GMT

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.

Re: ProductX returning unexpected results when iterating over date.

lbendlin — Tue, 11 Jul 2023 00:08:06 GMT

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.

Re: ProductX returning unexpected results when iterating over date.

MKeppel — Tue, 11 Jul 2023 04:41:43 GMT

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

Re: ProductX returning unexpected results when iterating over date.

lbendlin — Tue, 11 Jul 2023 20:26:18 GMT

Here's an interesting article around the subject

Introducing ALLSELECTED in DAX - SQLBI