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'[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.
 
 

 

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

3 Replies

  • 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