cancel
Showing results for
Did you mean:

Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.

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

#### New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.

#### Fabric Monthly Update - May 2024

Check out the May 2024 Fabric update to learn about new features.

#### Fabric certifications survey

Certification feedback opportunity for the community.

Top Solution Authors
Top Kudoed Authors