topic Re: Last relative days in DAX Commands and Tips

Last relative days

Neha_12 — Mon, 12 Jun 2023 11:52:00 GMT

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

Re: Last relative days

tamerj1 — Mon, 12 Jun 2023 12:57:50 GMT

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

Re: Last relative days

eliasayyy — Mon, 12 Jun 2023 13:10:10 GMT

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

Re: Last relative days

Neha_12 — Tue, 13 Jun 2023 07:02:12 GMT

Thanks for the reply,

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

Re: Last relative days

tamerj1 — Tue, 13 Jun 2023 07:23:51 GMT

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

Re: Last relative days

Neha_12 — Tue, 13 Jun 2023 07:24:36 GMT

No, simple table

Re: Last relative days

tamerj1 — Tue, 13 Jun 2023 07:30:37 GMT

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

Re: Last relative days

Neha_12 — Wed, 14 Jun 2023 15:23:14 GMT

PFA the screenshots

Thanks

Re: Last relative days

tamerj1 — Wed, 14 Jun 2023 16:17:39 GMT

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

Re: Last relative days

Neha_12 — Tue, 20 Jun 2023 14:04:09 GMT

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

Re: Last relative days

tamerj1 — Tue, 20 Jun 2023 14:07:28 GMT

Neha_12
No need to connect