Forum Discussion

sthandiwe's avatar
sthandiwe
Frequent Visitor
6 years ago
Solved

5 Day Rolling Period With Multiple Days Selected

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'[A...
Need Help
Tips and Tricks
Tutorial Requests
  • v-gizhi-msft's avatar
    v-gizhi-msft
    6 years ago

    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

v-gizhi-msft's avatar
v-gizhi-msft
Icon for Community Support rankCommunity Support
6 years ago

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