Power BI is turning 10! Tune in for a special live episode on July 24 with behind-the-scenes stories, product evolution highlights, and a sneak peek at what’s in store for the future.
Save the dateEnhance your career with this limited time 50% discount on Fabric and Power BI exams. Ends August 31st. Request your voucher.
Hey!
I have been struggling with this problem for a while. I would like to create a rolling average of this measure here. Basically the measure is transforming the sales to index starting from 100 for selected time period. The measure works well, but when I try to create a rolling average of it I don't get the rolling average to work. I have tried countless of options but none seems to work.
Index Sales =
VAR _FirstDate =
CALCULATE(MIN(vDimCalendar[Date]),
ALLSELECTED(vDimCalendar[Date])
)
VAR ThisDate = SELECTEDVALUE(vDimCalendar[Date])
VAR BaseValue =
CALCULATE(
SUM(CubeSales[ACT]),
DATEADD(vDimCalendar[Date], _FirstDate - ThisDate, DAY))
VAR CurrentValue = CALCULATE(SUM(CubeSales[ACT]))
VAR IndexedValue = DIVIDE(CurrentValue, BaseValue, BLANK()) * 100
RETURN
IndexedValue
Solved! Go to Solution.
Hi @Anonymous
Please try
Index Sales Running Average =
VAR CurrentDate =
MAX ( vDimCalendar[Date] )
VAR SelectedDates =
ALLSELECTED ( vDimCalendar[Date] )
VAR BaseValue =
CALCULATE ( SUM ( CubeSales[ACT] ), SelectedDates )
VAR DatesBefore =
FILTER ( SelectedDates, vDimCalendar[Date] <= CurrentDate )
VAR Result =
AVERAGEX (
DatesBefore,
VAR ThisDate = vDimCalendar[Date]
VAR CurrentValue =
CALCULATE (
SUM ( CubeSales[ACT] ),
ALL ( vDimCalendar ),
vDimCalendar[Date] = ThisDate
)
RETURN
DIVIDE ( CurrentValue, BaseValue )
)
RETURN
Result
Hi @Anonymous
Please try
Index Sales Running Average =
VAR CurrentDate =
MAX ( vDimCalendar[Date] )
VAR SelectedDates =
ALLSELECTED ( vDimCalendar[Date] )
VAR BaseValue =
CALCULATE ( SUM ( CubeSales[ACT] ), SelectedDates )
VAR DatesBefore =
FILTER ( SelectedDates, vDimCalendar[Date] <= CurrentDate )
VAR Result =
AVERAGEX (
DatesBefore,
VAR ThisDate = vDimCalendar[Date]
VAR CurrentValue =
CALCULATE (
SUM ( CubeSales[ACT] ),
ALL ( vDimCalendar ),
vDimCalendar[Date] = ThisDate
)
RETURN
DIVIDE ( CurrentValue, BaseValue )
)
RETURN
Result
Hey!
This seems to return correct result. I modified it a bit since it was returning the result for whole timeframe. Also it seems to be very slow, so I might try to optimize it. Anyway thanks for help!
Index Sales Running Average =
VAR CurrentDate =
MAX ( vDimCalendar[Date] )
VAR SelectedDates =
ALLSELECTED ( vDimCalendar[Date] )
VAR BaseValue =
CALCULATE ( SUM ( CubeSales[ACT] ), SelectedDates )
VAR DatesBefore =
FILTER ( SelectedDates, vDimCalendar[Date] <= CurrentDate )
VAR Result =
AVERAGEX (
DatesBefore,
VAR ThisDate = vDimCalendar[Date]
VAR CurrentValue =
CALCULATE (
SUM ( CubeSales[ACT] ),
ALL ( vDimCalendar ),
vDimCalendar[Date] = ThisDate
)
RETURN
DIVIDE ( CurrentValue, BaseValue ) * 1000
)
RETURN
IF(CurrentDate <= MAX(CubeSales[Date]),
Result,
BLANK())
Here is something I have tried, but this does not return the correct output.
6 Months Rolling average Index =
AVERAGEX(
WINDOW(
-6, REL,
0, REL,
ORDERBY (vDimCalendar[Date], ASC)
),
CALCULATE([Index Sales])
)
can you try below dax:
RollingAverageIndexedValue = VAR _WindowSize = 7 -- Adjust the window size as needed RETURN AVERAGEX( FILTER( ALLSELECTED(vDimCalendar), vDimCalendar[Date] >= MIN(vDimCalendar[Date]) - _WindowSize + 1 && vDimCalendar[Date] <= MAX(vDimCalendar[Date]) ), [IndexedValue] )
If this helped, Subscribe AnalyticPulse on YouTube for future updates:
https://www.youtube.com/@AnalyticPulse
https://instagram.com/analytic_pulse
https://analyticpulse.blogspot.com/
subscribe to Youtube channel For fun facts:
https://www.youtube.com/@CogniJourney
Hey!
Unfortunatelly the measure you provided does not work. It returns 100 for each date.
User | Count |
---|---|
25 | |
12 | |
8 | |
6 | |
6 |
User | Count |
---|---|
26 | |
12 | |
11 | |
8 | |
6 |