Forum Discussion
Ignore Page Level Filter on a Power BI Calculation Group
Problem is that I have a 14 w Rolling Average, that when the 1st week in 2021 is selected, it does not work .
Code for measure
CALCULATE (
DISTINCTCOUNT ( Sales[activity_id] ),
FILTER (
Sales,
Sales[done] = "Yes"
&& Sales[activity_type]
IN { "In-Person Meeting", "Virtual Meeting", "Site Visit" }
) )
Code for Caluclation group
VAR __lastVisibleDate =
LASTDATE ( 'Calendar'[Date] )
VAR _fromDate =
CALCULATE ( DATEADD ( __lastVisibleDate, -91, DAY ), ALL ( 'Time') )
VAR __result =
CALCULATE (
SELECTEDMEASURE (),
REMOVEFILTERS ( 'Calendar' ),
'Calendar'[Date] > _fromDate
&& 'Calendar'[Date] <= __lastVisibleDate
)
VAR _result_divide =
DIVIDE ( __result, 13, 0 )
RETURN
IF ( _result_divide < 0.1, BLANK (), _result_divide )I have tried every combination of ALL('Calendar' ) to get the rolling 14 week average to work for the 1st week of Jan 2021 . Debugging the _fromDate does give me the 20/10/2020 but the rolling average still does not work out correctly ! Argghhh !
Any ideas ? This is proving quite diffcicult
The PAGE level filter is needed to allow the user to select the financial year .
JimmyKhan2022 , If you do not want more week then selected then you can get avg with help date/calendar table
14 Month Avg = CALCULATE(AverageX(Values('Date'[MONTH Year]),calculate(Sum('Table'[Value)))
,DATESINPERIOD('Date'[Date ],MAX('Date'[Date ]),-14,MONTH))Or window function
Power BI Window function Rolling, Cumulative/Running Total, WTD, MTD, QTD, YTD, FYTD: https://youtu.be/nxc_IWl-tTc
1 Reply
- amitchandak
Super User
JimmyKhan2022 , If you do not want more week then selected then you can get avg with help date/calendar table
14 Month Avg = CALCULATE(AverageX(Values('Date'[MONTH Year]),calculate(Sum('Table'[Value)))
,DATESINPERIOD('Date'[Date ],MAX('Date'[Date ]),-14,MONTH))Or window function
Power BI Window function Rolling, Cumulative/Running Total, WTD, MTD, QTD, YTD, FYTD: https://youtu.be/nxc_IWl-tTc