topic Re: Filtered DateDIFF in DAX Commands and Tips

Filtered DateDIFF

pawelj795 — Tue, 17 Dec 2019 11:03:26 GMT

Hi.
I want to calculate measure which show a date difference between today and last transaction on particular item.
But to this calculation I want to take only specified type of transactions (TransType = 0 or 9) ->it's determine on every row.

So my question is, how to filter datediff function?

I only figured out some part of this measure:

DATEDIFF(TODAY();MAX(Invent_Trans[Date_Physical]);DAY)

I thought that use VAR might be helpful.

Re: Filtered DateDIFF

az38 — Tue, 17 Dec 2019 11:23:35 GMT

hi pawelj795

try a measure

=DATEDIFF( TODAY(); calculate(MAX(Invent_Trans[Date_Physical]);OR(Invent_Trans[TransType] = 0; Invent_Trans[TransType]9));DAY )

do not hesitate to give a kudo to useful posts and mark solutions as solution

Re: Filtered DateDIFF

Anonymous — Tue, 17 Dec 2019 11:25:48 GMT

pawelj795
if you want a column use this one

Column = IF(Invent_Trans[TransType] IN {0,9},DATEDIFF(Invent_Trans[Date_physical],TODAY(),DAY))

in case you are looking for measure

Measure = IF(MAX(Invent_Trans[TransType]) IN {0,9},DATEDIFF(MAX(Invent_Trans[Date_physical]),TODAY(),DAY))

Re: Filtered DateDIFF

hohlick — Tue, 17 Dec 2019 11:28:52 GMT

Just wrap it in CALCULATE and apply an appropriate filter, for example:

= CALCULATE ( DATEDIFF ( TODAY (); MAX ( Invent_Trans[Date_Physical] ); DAY ); TREATAS ( { 0; 9 }; Invent_Trans[TransType] ) )

or you can use this syntax:

= CALCULATE ( DATEDIFF ( TODAY (); MAX ( Invent_Trans[Date_Physical] ); DAY ); FILTER ( VALUES ( Invent_Trans[TransType] ); Invent_Trans[TransType] = 0 || Invent_Trans[TransType] = 9 ) )

Re: Filtered DateDIFF

pawelj795 — Tue, 17 Dec 2019 12:09:35 GMT

Thanks guys, thats works perfectly !

I want to also add aging to this measure.

I tried the simpliest way to do this by use IF function, but it's not working correctly.

Aging = IF(WH_Invent_Trans[Grouping]<-120; " 120+";

IF(WH_Invent_Trans[Grouping] <-60 && WH_Invent_Trans[Grouping]>=-120; " 61-120";

IF(WH_Invent_Trans[Grouping]<-30 && WH_Invent_Trans[Grouping]>=-60; " 31-60";

IF(WH_Invent_Trans[Grouping] <-15 && WH_Invent_Trans[Grouping]>=-30; " 16-30";

IF(WH_Invent_Trans[Grouping] < 0 && WH_Invent_Trans[Grouping]>=-15; "0-15";

BLANK())))))

Where is my mistake?
The other thing, should I use measure or column if I used measure to calculate datedifference.

I also must add one filter to Anonymous measure.
The transactions must have WH_Invent_Trans[QTY]<>BLANK()

How to resolve this?

Re: Filtered DateDIFF

Anonymous — Tue, 17 Dec 2019 12:22:29 GMT

pawelj795 use switch statement for grouping

Measure 2 = SWITCH(TRUE() ,[Measure]<-120,"120" ,[Measure]<-60 && [Measure]>=-120,"61-120" ,BLANK())

Add remaining consitions in switch function

In order to include qty <> blank

IF(AND(MAX(Invent_Trans[TransType]) IN {0,9},MAX(Invent_Trans[QTY])<>BLANK()),DATEDIFF(MAX(Invent_Trans[Date_physical]),TODAY(),DAY))

Re: Filtered DateDIFF

pawelj795 — Tue, 17 Dec 2019 12:46:48 GMT

Anonymous
Something is wrong with Aging>
It shows only blank values.

Aging =

SWITCH(TRUE();

[Grouping]<-120;" 120+";

[Grouping]<-60 && [Grouping]>=-120;" 61-120";

[Grouping]<-30 && [Grouping]>=-60;" 31-60";

[Grouping]<-15 && [Grouping]>=-30;" 16-30";

[Grouping]< 0 && [Grouping]>=-15;"0-15";

BLANK())

Grouping =

IF(AND(MAX(WH_Invent_Trans[TransType]) IN {0;9}; MAX(WH_Invent_Trans[QTY])<>BLANK()); DATEDIFF(MAX(WH_Invent_Trans[Date Physical]);TODAY();DAY))

Re: Filtered DateDIFF

Anonymous — Tue, 17 Dec 2019 12:50:10 GMT

pawelj795 If you are using today() as second argument in datediff function then you must be getting positive values. please check what is the output of datediff measure. Is it positive number or negative and then accordingly modify the switch function conditions

Re: Filtered DateDIFF

pawelj795 — Tue, 17 Dec 2019 13:04:03 GMT

Anonymous
Haha, so stupid mistake.
I didn't notice that I had only positive values 😅.

The last thing about this topic,
How to visualize this measure?

I want to make Pie chart with Aging as Legend and current index values as Values but because Agins is a measure, that means it can't be use this way.

Do you have any creative idea to show my results?

Re: Filtered DateDIFF

pawelj795 — Tue, 17 Dec 2019 14:26:36 GMT

Maybe should I use column instead of measures for whole calculation?

Re: Filtered DateDIFF

Anonymous — Wed, 18 Dec 2019 04:13:03 GMT

pawelj795 not sure what is the index value field in your dataset but you should create a calculated column for aging if you want to use that field as legend.

Re: Filtered DateDIFF

pawelj795 — Wed, 18 Dec 2019 13:45:59 GMT

Anonymous

Calculated Column isn't solution. It filtered incorrectly my entire report.

I thought that in pie chart i could use one measure for every part of Aging.
I mean, it would be 5 consecutive measures:
1.Aging (0,15)
2.Aging (16,30)

3.Aging(31-60)

4. Aging(61-120)
5.Aging(120+)

My question is how to combine 3 below measures into one.

SWITCH(TRUE();

[Grouping]>120;" 120+";

[Grouping]>60 && [Grouping]<=120;" 61-120";

[Grouping]>30 && [Grouping]<=60;" 31-60";

[Grouping]>15 && [Grouping]<=30;" 16-30";

[Grouping]> 0 && [Grouping]<=15;"0-15";

BLANK())

IF(AND(MAX(WH_Invent_Trans[TransType]) IN {0;9}; MAX(WH_Invent_Trans[QTY])<>BLANK()); DATEDIFF(MAX(WH_Invent_Trans[Date Physical]);TODAY();DAY))

IF( MAX(DimDates[Date])<=TODAY()-1;

CALCULATE(

SUM(

WH_Invent_Trans[Inventory Value EUR]);

DATESYTD(DimDates[Date]));

BLANK())