Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

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

Reply
pawelj795
Post Prodigy
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
pawelj795
Post Prodigy
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())




 

 

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

 

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

 

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


 

aging.JPG

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

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

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.

az38
Community Champion
Community Champion

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


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

Helpful resources

Announcements
RTI Forums Carousel3

New forum boards available in Real-Time Intelligence.

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

MayPowerBICarousel

Fabric Monthly Update - May 2024

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

LearnSurvey

Fabric certifications survey

Certification feedback opportunity for the community.

Top Solution Authors