Forum Discussion

pawelj795's avatar
pawelj795
Post Prodigy
6 years ago

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

  • Anonymous's avatar
    Anonymous
    Not applicable
    6 years ago

    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's avatar
      pawelj795
      Post Prodigy
      6 years ago

      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's avatar
        Anonymous
        Not applicable
        6 years ago

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

         

  • az38's avatar
    az38
    Community Champion
    6 years ago

    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

  • hohlick's avatar
    hohlick
    Continued Contributor
    6 years ago

    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
    )
)
  • pawelj795's avatar
    pawelj795
    Post Prodigy
    6 years ago

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