Last relative days

[Neha_12]

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

[eliasayyy]

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"))

[tamerj1]

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]

Thanks for the reply,

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

[tamerj1]

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

[Neha_12]

No, simple table 

[tamerj1]

@Neha_12 
Please provide a screenshot that shows all the columns invloved and clarifies from which tables.

[Neha_12]

PFA the screenshots 

Thanks

 

[tamerj1]

@Neha_12 

First, manually add the slicer table which is a single column table that contains the integers from 1 to let's say 10

you need to select two values from the slicer in order to get different results in the cards

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

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

[Neha_12]

If I create a separate slicer value table, how will I connect with the main table?

[tamerj1 · Accepted solution]

@Neha_12 
No need to connect