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

Get certified in Microsoft Fabric—for free! For a limited time, the Microsoft Fabric Community team will be offering free DP-600 exam vouchers. Prepare 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
OCT PBI Update Carousel

Power BI Monthly Update - October 2024

Check out the October 2024 Power BI update to learn about new features.

September Hackathon Carousel

Microsoft Fabric & AI Learning Hackathon

Learn from experts, get hands-on experience, and win awesome prizes.

October NL Carousel

Fabric Community Update - October 2024

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

Top Kudoed Authors