Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
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,
User | Count |
---|---|
140 | |
113 | |
104 | |
76 | |
63 |
User | Count |
---|---|
135 | |
126 | |
110 | |
70 | |
61 |