Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
I have the following for my 12Month trailing measure.
12Mo Trailing Sales = CALCULATE ( [1Sales], DATESBETWEEN ( 'Value Entry'[Posting Date].[Date], NEXTDAY ( SAMEPERIODLASTYEAR ( LASTDATE ( 'Value Entry'[Posting Date].[Date] ) ) ), LASTDATE ( 'Value Entry'[Posting Date].[Date] ) ))
Works great if I want to look on all the years I have data on, exept for the first year...
When adding a filter in the report, for example only look on year 2018, the measure ignores 2017 and the trailing measure is for 2018 is incorrect, since it isn't using the data for 2017.
ALso tried to ignore filters for the data that DATESBETWEEN uses:
12Mo Trailing Sales = CALCULATE ( [1Sales], DATESBETWEEN ( ALL('Value Entry', 'Value Entry'[Posting Date].[Date]), NEXTDAY ( SAMEPERIODLASTYEAR ( LASTDATE ( 'Value Entry'[Posting Date].[Date] ) ) ), LASTDATE ( 'Value Entry'[Posting Date].[Date] ) ))
But it gives me error:
DatesBetween and DatesInPeriod functions are only accepting date column reference as a first argument.
Any ideas?
Solved! Go to Solution.
Hi @MrMarshall,
Maybe you can try to create a calendar table with continual dates, create a relationship between calendar table and data table ('Value Entry'). Refer to date column in calendar table rather than 'Value Entry' in your measure. Also, add dates from calendar table into Matrix and slicer.
Regards,
Yuliana Gu
Hi @MrMarshall,
Maybe you can try to create a calendar table with continual dates, create a relationship between calendar table and data table ('Value Entry'). Refer to date column in calendar table rather than 'Value Entry' in your measure. Also, add dates from calendar table into Matrix and slicer.
Regards,
Yuliana Gu
Hi Yuliana
@v-yulgu-msft , this worked.
Can you eleborate why it works when we refer to the date in the calendar table ( in the measure, slicer & matrix)?
thanks.
Worked!
12Mo Trailing Sales = CALCULATE ( [1Sales], DATESBETWEEN ( 'Value Entry'[Posting Date], NEXTDAY ( SAMEPERIODLASTYEAR ( LASTDATE ( 'Value Entry'[Posting Date].[Date] ) ) ), LASTDATE ( 'Value Entry'[Posting Date].[Date] ) ))
A calendar table with the DAX above.
Thank you!
Hi! I am afraid that didn't help.
Try using the following formula:
12Mo Trailing Sales = CALCULATE ( [1Sales], DATESBETWEEN ( 'Value Entry'[Posting Date], NEXTDAY ( SAMEPERIODLASTYEAR ( LASTDATE ( 'Value Entry'[Posting Date].[Date] ) ) ), LASTDATE ( 'Value Entry'[Posting Date].[Date] ) ))
Hi!
Thanks for the reply.
Using the formula above gets me the same result for the trailing value and the "normal" sales value.
So unfortunately not a trailing value.
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
110 | |
94 | |
82 | |
66 | |
58 |
User | Count |
---|---|
151 | |
121 | |
104 | |
87 | |
67 |