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.
Hello,
Newbie here.
I am running into a problem with trying to create a measure that is related to another measure.
My goal is to create a line chart with the X axis being the week number and Y axis being the averaged running total (+/- 14 days).
net_quantity_running_total in Date = CALCULATE( SUM('Table A'[net_quantity]), FILTER( ALL('Date'), ISONORAFTER('Date'[Date], MAX('Date'[Date]), DESC) ) )
net_quantity_running_total_rolling_average in Date = IF( ISFILTERED('Date'[Date]), ERROR("Time intelligence quick measures can only be grouped or filtered by the Power BI-provided date hierarchy or primary date column."), VAR __LAST_DATE = LASTDATE('Date'[Date].[Date]) RETURN AVERAGEX( DATESBETWEEN( 'Date'[Date].[Date], DATEADD(__LAST_DATE, -14, DAY), DATEADD(__LAST_DATE, 14, DAY) ), CALCULATE([net_quantity_running_total in Date]) ) )
I assume it is because the WeekNumber is not actually part of the Date Hierarchy. Is there a way to add the week number to the date hierarchy?
Thanks,
Solved! Go to Solution.
hi, @Anonymous
Just adjust the formula as below:
net_quantity_running_total_rolling_average in Date = IF( ISFILTERED('Date'[Date]), ERROR("Time intelligence quick measures can only be grouped or filtered by the Power BI-provided date hierarchy or primary date column."), VAR __LAST_DATE = LASTDATE('Date'[Date]) RETURN AVERAGEX( DATESBETWEEN( 'Date'[Date], DATEADD(__LAST_DATE, -14, DAY), DATEADD(__LAST_DATE, 14, DAY) ), CALCULATE([net_quantity_running_total in Date]) ) )
Regards,
Lin
hi, @Anonymous
Just adjust the formula as below:
net_quantity_running_total_rolling_average in Date = IF( ISFILTERED('Date'[Date]), ERROR("Time intelligence quick measures can only be grouped or filtered by the Power BI-provided date hierarchy or primary date column."), VAR __LAST_DATE = LASTDATE('Date'[Date]) RETURN AVERAGEX( DATESBETWEEN( 'Date'[Date], DATEADD(__LAST_DATE, -14, DAY), DATEADD(__LAST_DATE, 14, DAY) ), CALCULATE([net_quantity_running_total in Date]) ) )
Regards,
Lin
Thank you Lin! That was it!
What is the difference when you reference the date each way?
'Date'[Date].Date
'Date'[Date]
Wouldn't these give you the same thing?
Thanks,
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 |
---|---|
106 | |
94 | |
77 | |
65 | |
53 |
User | Count |
---|---|
147 | |
106 | |
104 | |
87 | |
61 |