Rolling 30 days sales on MTD Dates

[Avishek07]

Hi All..

I have a use case which I can explain...

I have a single select slicer from calender date table: say I select 5th March 2023

I have a bar chart...where in I have to show rolling 30 days sales...but there are 2 catches here:

1. The dates on the x-axis of the bar should be MTD dates...i.e in my case it should be from 1st March 2023 to 5th March 2023...

2. The rolling 30 days sales should be relative to each day of the axis i.e bar on 1st march should show sales from 1st frb to 1st march...on 2nd march bar should show 2nd feb to 2nd march......on 5th march it should be from 5th feb to 5th march....can u pls help me to write the dax

[Avishek07]

I need rolling 30 days...but the code above does not give me the apt output...Sorry, I am unable to attach sample data for u..

What I need is..I need to clip the x-axis to MTD dates...say if u select 6th June...my axis should dynamically show 1st to 6th...if somebody selects 30th june..it shud have 1st to 30th data points in x-axis..and from each..i want to rollback and find the avg sales...

so the bar on 30th june shud be avg sales b/w 1st to 30th June...bar on 5th june will avg sales from 5th may to 5th june...

can u share the dax for that pls

[danextian]

Hi @Avishek07 ,

From nth day of the month to nth day of the next month is not always  30 days but if you wanto just sum the rolling 30 days, you should subtract 29 from the date in the current row.  Below is a sample formula. Just change the XDays variable to suit your need.

Rolling 30days =
VAR XDays = 29
RETURN
    CALCULATE (
        [Sum of Values],
        FILTER (
            ALL ( Dates ),
            Dates[Date]
                >= MAX ( Dates[Date] ) - XDays
                && Dates[Date] <= MAX ( Dates[Date] )
        )
    )

Make sure to select the correct style in the slicer setting