Filtered DateDIFF

[pawelj795]

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.

[pawelj795]

@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.

Spoiler

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

[hohlick]

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

[Anonymous]

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

[pawelj795]

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.

Spoiler

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?

[Anonymous]

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

[pawelj795]

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

Spoiler

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

[Anonymous]

@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

[pawelj795]

@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?

[pawelj795]

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

[Anonymous]

@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.

[az38]

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