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

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
CloudMonkey
Post Prodigy
Post Prodigy

Why is .Date breaking this formula?

Hello,

 

I've uploaded my pbix file here -> https://filebin.net/zn0vkabg326m5m57

 

I have the below formula which works to find a 2 day average:

 

 

Moving average 3 = 

VAR
FilterContext_MaxDate = max('Product'[Date])

VAR
FilterContext_MinDate = FilterContext_MaxDate - 1

RETURN
calculate(average('Product'[Volume]),filter(All('Product'[Date]),
    'Product'[Date] >= FilterContext_MinDate &&
    'Product'[Date] <= FilterContext_MaxDate
))

 

 

But if I change the 4th line to this (by putting .Date at the end) then it breaks:

FilterContext_MaxDate = max('Product'[Date].[Date])
 
Please can you tell me why adding .Date breaks the formula?
 
desktop 1902pm.JPG
Thank you for your help,
 
Cloud Monkey
 
1 ACCEPTED SOLUTION

Hi @CloudMonkey ,

Please refer to this post: when to use Date.[date] ?

 

Best Regards,

Icey

 

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

View solution in original post

4 REPLIES 4
Icey
Community Support
Community Support

Hi @CloudMonkey,

Based on my test, it is caused by that the results returned are different.

date.PNG

 

Best Regards,

Icey

 

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Sorry I must be missing some knowledge. Why would 'Product'[Date].[Date] provide a different date to 'Product'[Date]?

 

'Product'[Date] is stored as a date.

 

Thanks,

 

CM

Hi @CloudMonkey ,

Please refer to this post: when to use Date.[date] ?

 

Best Regards,

Icey

 

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

kentyler
Solution Sage
Solution Sage

Product[Date] means the date field in the product table

what does Product[Date].[Date] mean ?

are you using an automatically generated date hierarchy ?





Did this post answer your question? Mark it as a solution so others can find it!

Help when you know. Ask when you don't!




Join the conversation at We Talk BI find out more about me at Slow BI


Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

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