cancel
Showing results for
Did you mean:  Post Prodigy

## Filtered DateDIFF

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.

11 REPLIES 11  Post Prodigy

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

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
)
)``````
Maxim Zelensky
excel-inside.pro Anonymous
Not applicable

@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))``  Post Prodigy

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
Not applicable

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

@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
Not applicable

@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  Post Prodigy

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

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?  Post Prodigy

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

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

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

do not hesitate to give a kudo to useful posts and mark solutions as solution Announcements #### Exclusive opportunity for Women!

Join us for a free, hands-on Microsoft workshop led by women trainers for women where you will learn how to build a Dashboard in a Day! #### Power Platform Conference-Power BI and Fabric Sessions

Join us Oct 1 - 6 in Las Vegas for the Microsoft Power Platform Conference. Top Solution Authors
Top Kudoed Authors
Users online (1,670)