Help

Anonymous this was a great post put on by you which I have used. I rolling 13 months. I need the data to be a Rolling 12 months.

How can I do this please?

Can Anonymous or anyone else help?

This is the link - https://community.powerbi.com/t5/Desktop/Rolling-13-months-DAX/td-p/663677

Thanks

Need Help

v-frfei-msft

6 years ago

Hi Anonymous ,

Please update the measures like this.

2Sales Rolling 12 ACT = 
VAR Maximum_Date =
    MAX ( 'Rolling Calendar'[Date] )
VAR Minimum_Date =
    EDATE(Maximum_Date,-11)
VAR Selected_Date =
    MAX ( 'Calendar'[Date] )
RETURN
    IF (
        Selected_Date <= Maximum_Date
            && Selected_Date >= Minimum_Date,
        [Sales ACT],
        BLANK ()
    )

2Sales Rolling 12 Month ACT (Totals) = 
IF (
    HASONEFILTER ( 'Calendar'[Date] ),
    [2Sales Rolling 12 ACT],
    SUMX ( 'Calendar', [2Sales Rolling 12 ACT] )
)

Also please check the pbix as attached.

Rolling 13 in DAX.pbix106 KB

3 Replies

v-frfei-msft

Community Support

6 years ago

Hi Anonymous ,

Please update the measures like this.

2Sales Rolling 12 ACT = 
VAR Maximum_Date =
    MAX ( 'Rolling Calendar'[Date] )
VAR Minimum_Date =
    EDATE(Maximum_Date,-11)
VAR Selected_Date =
    MAX ( 'Calendar'[Date] )
RETURN
    IF (
        Selected_Date <= Maximum_Date
            && Selected_Date >= Minimum_Date,
        [Sales ACT],
        BLANK ()
    )

2Sales Rolling 12 Month ACT (Totals) = 
IF (
    HASONEFILTER ( 'Calendar'[Date] ),
    [2Sales Rolling 12 ACT],
    SUMX ( 'Calendar', [2Sales Rolling 12 ACT] )
)

Also please check the pbix as attached.

Rolling 13 in DAX.pbix106 KB

Anonymous
Not applicable
6 years ago
v-frfei-msft brilliant thanks

Anonymous

Not applicable

6 years ago

Hi Anonymous
Would it help to use

Sales Rolling 12 ACT = 
VAR Maximum_Date =
    MAX ( 'Rolling Calendar'[Date] )
VAR Minimum_Date =
    DATE ( YEAR ( Maximum_Date ) - 1; MONTH ( Maximum_Date ); DAY( Maximum_Date ) )
VAR Selected_Date =
    MAX ( 'Calendar'[Date] )
RETURN
    IF (
        Selected_Date <= Maximum_Date
            && Selected_Date > Minimum_Date;
        [Sales ACT];
        BLANK ()
    )

Forum Discussion

Help

v-frfei-msft brilliant thanks