Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Be one of the first to start using Fabric Databases. View on-demand sessions with database experts and the Microsoft product team to learn just how easy it is to get started. Watch now

Reply
MKeppel
Frequent Visitor

ProductX returning unexpected results when iterating over date.

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  

 

MKeppel_0-1686694836098.png

MKeppel_1-1686694898549.png

 

Link to the two PBIX Files

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

 
 

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...
 
1 ACCEPTED SOLUTION
lbendlin
Super User
Super User

get rid of the Auto Date/Time hierarchy.

 

 

View solution in original post

10 REPLIES 10
lbendlin
Super User
Super User

get rid of the Auto Date/Time hierarchy.

 

 

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?

Disable auto date/time globally.

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?

Can you provide a pbix that demonstrates the issue?

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.

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.

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

Here's an interesting article around the subject

 

Introducing ALLSELECTED in DAX - SQLBI

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

 

 

Helpful resources

Announcements
Las Vegas 2025

Join us at the Microsoft Fabric Community Conference

March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount!

November Carousel

Fabric Community Update - November 2024

Find out what's new and trending in the Fabric Community.

Dec Fabric Community Survey

We want your feedback!

Your insights matter. That’s why we created a quick survey to learn about your experience finding answers to technical questions.