5 Day Rolling Period With Multiple Days Selected

[sthandiwe]

Hi guys.

I am trying to display values for a 5 day rolling period for data that has a value (AA, BB or CC). I calculated the 5-day rolling sum using:

AA Rolling 5 day = CALCULATE(SUM('Dashboard'[AA]),

FILTER( ALL('Dashboard'[transDate]),

'Dashboard'[transDate]>=MAX('Dashboard'[transDate])-5

&& 'Dashboard'[transDate]<=MAX('Dashboard'[transDate])))

I wish the be able to get the total without having the manually select multiple dates. So if I select a day, then results for the previous 5 days must appear. 

I have attached a picture.

[v-gizhi-msft · Accepted solution]

Hi,

According to your description, i create a sample to test:

Then create a seperate date slicer table:

DateSlicer = DISTINCT(SELECTCOLUMNS('Table',"Date",'Table'[transDate]))

Create a new category table by Enter Data:

Then try this measure:

Measure = 
SWITCH (
    SELECTEDVALUE ( 'New Category'[New Category] ),
    "AA Rolling 5 Day", CALCULATE (
        SUM ( 'Table'[Values] ),
        FILTER (
            'Table',
            'Table'[Category] = "AA"
                && 'Table'[transDate]
                    >= SELECTEDVALUE ( DateSlicer[Date] ) - 5
                && 'Table'[transDate] <= SELECTEDVALUE ( DateSlicer[Date] )
        )
    ),
    "BB Rolling 5 Day", CALCULATE (
        SUM ( 'Table'[Values] ),
        FILTER (
            'Table',
            'Table'[Category] = "BB"
                && 'Table'[transDate]
                    >= SELECTEDVALUE ( DateSlicer[Date] ) - 5
                && 'Table'[transDate] <= SELECTEDVALUE ( DateSlicer[Date] )
        )
    ),
    "CC Rolling 5 Day", CALCULATE (
        SUM ( 'Table'[Values] ),
        FILTER (
            'Table',
            'Table'[Category] = "CC"
                && 'Table'[transDate]
                    >= SELECTEDVALUE ( DateSlicer[Date] ) - 5
                && 'Table'[transDate] <= SELECTEDVALUE ( DateSlicer[Date] )
        )
    )
)

When you select one value in seperate date slicer, it shows:

Here is my test pbix file:

pbix 

Hope this helps.

Best Regards,

Giotto

[sthandiwe]

Thank you so much @v-gizhi-msft .

How do I get the row total?

It is coming up blank.

[Pragati11]

HI @sthandiwe ,

There is already a thread for similar kind of issue as follows:

https://community.powerbi.com/t5/Desktop/Rolling-5-days-back/m-p/827160

try modifying your DAX expression to the one mentioned in this thread.

If this helps and resolves the issue, appreciate a Kudos and mark it as a Solution! 🙂

Thanks,

Pragati