Forum Discussion

Neha_12's avatar
Neha_12
Frequent Visitor
3 years ago
Solved

Last relative days

Hi,

I have a relative date slicer and two card visuals   whenver user selects last 1 day on a slicer first card should show a previous date data and second card should show previous two days data

For and instance if current date is 12 June

First card should display 11th  June's data and second card should display 10th June's data.

Similarly if user selects last 2 days on a slicer , First card should display 11th to 10 th June's data and second card to show 9th to 8th June's data, how do I acheive this, please help tamerj1 , Greg_Deckler 

PFA the excel

 

Thanks

10 Replies

  • tamerj1's avatar
    tamerj1
    Icon for Community Champion rankCommunity Champion
    3 years ago

    Hi Neha_12 

    please try

     

    Card 1 =
    SUMX (
    FILTER (
    ALL ( 'Date'[Date] ),
    'Date'[Date]
    <= MAX ( 'Date'[Date] ) + MIN ( Slicer[Value] )
    && 'Date'[Date]
    >= MIN ( 'Date'[Date] ) + MIN ( Slicer[Value] )
    ),
    [Measure]
    )

    Card 2 =
    SUMX (
    FILTER (
    ALL ( 'Date'[Date] ),
    'Date'[Date]
    <= MAX ( 'Date'[Date] ) + MAX ( Slicer[Value] )
    && 'Date'[Date]
    >= MIN ( 'Date'[Date] ) + MAX ( Slicer[Value] )
    ),
    [Measure]
    )

    • Neha_12's avatar
      Neha_12
      Frequent Visitor
      3 years ago

      Thanks for the reply,

      Can you please tell me, what should be in this min and max(slicer[value])?

      • tamerj1's avatar
        tamerj1
        Icon for Community Champion rankCommunity Champion
        3 years ago

        Neha_12 
        Last Relative Days. I guess this is a parameter table, Am I right?

  • eliasayyy's avatar
    eliasayyy
    Icon for Memorable Member rankMemorable Member
    3 years ago

    hello Neha_12 this is what you want? if yes then i will show the measure below




     

    Measure for card 1 = 
VAR _i = MAX('Calendar'[Index])
VAR _c = COUNT('Calendar'[Date])
VAR _n = CALCULATE( [Total] , REMOVEFILTERS('Calendar'[Date]),'Calendar'[Index] = _i - _c)
VAR _m = CALCULATE( [Total] , REMOVEFILTERS('Calendar'[Date]),AND('Calendar'[Index]  >= _i - _c, 'Calendar'[Index] < _i ))
VAR result = IF(_c = 1 , _n , _m)
RETURN
result

     

    Measure for card 2 = 
VAR _i = MAX('Calendar'[Index])
VAR _c = COUNT('Calendar'[Date])
VAR _n = CALCULATE( [Total] , REMOVEFILTERS('Calendar'[Date]),'Calendar'[Index] = _i - _c - 1)
VAR _m = CALCULATE( [Total] , REMOVEFILTERS('Calendar'[Date]),AND('Calendar'[Index]  >= _i - _c*2, 'Calendar'[Index] < _i - _c ))
VAR result = IF(_c = 1 , _n , _m)
RETURN
result



    first create a new caelndar table with no duplicates , then in pwoer query , add a new index column 
    like this 


    create a relationship between date of main table and date of second table 

    and there you go , if you only wanna display the dates 

    use this measure

    Date 1 = 
VAR _i = MAX('Calendar'[Index])
VAR _c = COUNT('Calendar'[Date])
VAR _n = CALCULATE( MAX(calendar[date]) , REMOVEFILTERS('Calendar'[Date]),'Calendar'[Index] = _i - _c)
VAR _m = CALCULATE( MAX(calendar[date]) , REMOVEFILTERS('Calendar'[Date]),AND('Calendar'[Index]  >= _i - _c, 'Calendar'[Index] < _i ))
VAR result = IF(_c = 1 , _n , _m)
VAR _i2 = MAX('Calendar'[Index])
VAR _c2 = COUNT('Calendar'[Date])
VAR _n2 = CALCULATE( min(calendar[date]) , REMOVEFILTERS('Calendar'[Date]),'Calendar'[Index] = _i2 - _c2)
VAR _m2 = CALCULATE( min(calendar[date]) , REMOVEFILTERS('Calendar'[Date]),AND('Calendar'[Index]  >= _i2 - _c2, 'Calendar'[Index] <> _i2 ))
VAR result2 = IF(_c2 = 1 , _n2 , _m2)
RETURN
IF(result2<>result,"From "&FORMAT(result2,"DD/MM/YYYY") & " To " & FORMAT(result,"DD/MM/YYYY"),FORMAT(result2,"DD/MM/YYYY"))



    Date 2 = 
VAR _i = MAX('Calendar'[Index])
VAR _c = COUNT('Calendar'[Date])
VAR _n = CALCULATE( MAX(calendar[date]) , REMOVEFILTERS('Calendar'[Date]),'Calendar'[Index] = _i - _c - 1)
VAR _m = CALCULATE( MAX(calendar[date]) , REMOVEFILTERS('Calendar'[Date]),AND('Calendar'[Index]  >= _i - _c*2, 'Calendar'[Index] < _i - _c ))
VAR result = IF(_c = 1 , _n , _m)
VAR _i2 = MAX('Calendar'[Index])
VAR _c2 = COUNT('Calendar'[Date])
VAR _n2 = CALCULATE( min(calendar[date]) , REMOVEFILTERS('Calendar'[Date]),'Calendar'[Index] = _i2 - _c2 -1)
VAR _m2 = CALCULATE( min(calendar[date]) , REMOVEFILTERS('Calendar'[Date]),AND('Calendar'[Index]  >= _i2 - _c2*2, 'Calendar'[Index] <> _i2 - _c2 ))
VAR result2 = IF(_c2 = 1 , _n2 , _m2)
RETURN
IF(result2<>result,"From "&FORMAT(result2,"DD/MM/YYYY") & " To " & FORMAT(result,"DD/MM/YYYY"),FORMAT(result2,"DD/MM/YYYY"))