Forum Discussion

KristofferAJ's avatar
KristofferAJ
Helper III
4 years ago
Solved

Time between two dates with rules

Hi!

I have a question for a formula I would like to create. In theory it's only two dates which needs to be subtracted in order to provide time between 'Dispatch' and 'last sold date'

However I have a big wish to build in some restrictions without needing to set up additional tables with connections.

 

Formula wish: I want to know the time between 'Dispatch date' and 'last sold date' per Account ID, but I want to use the latest date per country for each calculation.

 

For instance:

For apples, account Mike:

Use 'Date dispatch': 4/17/2013, however use last date in his country Austria: 10/9/2013

 

I know this can be done in tableau by FIX to a specific column like country, but is there a way I can do this in DAX without needing to setup additional related tables?

Would appreciate input and help to this question. I have attached the data set here also

Thanks

 

FruitAccount IDCountryDispathFirst sold dateLast sold date
PearklaraAustria10/23/201311/11/20132/10/2014
OrangeKonradAustria7/18/20137/24/20138/5/2013
AppleMikeAustria4/17/20134/25/20134/29/2013
OrangePiaAustria8/7/20139/5/201310/9/2013
PearEvaFrance2/8/20143/17/20143/17/2014
OrangeJohnFrance8/28/20139/20/20139/20/2013
AppleShannonFrance5/29/20137/11/2013 
PearSimonFrance8/20/20139/18/201312/11/2013
PearChrisGermany1/29/20143/3/20143/3/2014
AppleJimGermany5/3/20137/8/20137/8/2013
PearLizGermany7/2/20137/29/201310/29/2013
OrangePaulGermany8/13/20138/23/20138/23/2013
AppleBeernardSpain5/29/20136/4/20137/24/2013
PearKarinSpain 8/21/201312/20/2013
OrangeSusanSpain7/16/20137/22/20139/24/2013
      
+ 20 other+ 1000 other+ 20 other   
  • Anonymous's avatar
    Anonymous
    4 years ago

    KristofferAJ Please make a small addition in the ALLEXCEPT() function in measure to take both fruit and country into consideration.

     

    DateDifference =
    VAR maxdate =
    CALCULATE (
    MAX ( Table1[LastSoldDate] ),
    ALLEXCEPT ( Table1, Table1[Country], Table1[Fruit] )
    )
    VAR leastdate =
    SELECTEDVALUE ( Table1[Dispatch] )
    VAR diff =
    CALCULATE (
    DATEDIFF ( leastdate, maxdate, DAY ),
    ALLEXCEPT ( Table1, Table1[Account ID] )
    )
    RETURN
    diff + 0 & " Days"
     
     
    Please accept it as a solution if it matches your requirement🙂

     

5 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    4 years ago

    KristofferAJ 

    I think the last date of Austria should be 02/10/2014 and not 10/9/2013 which you mentioned in your question.

     

    Below measure gives expected output in that case:

     

    DateDifference =
    VAR maxdate =
    CALCULATE (
    MAX ( Table1[LastSoldDate] ),
    ALLEXCEPT ( Table1, Table1[Country] )
    )
    VAR leastdate =
    SELECTEDVALUE ( Table1[Dispatch] )
    VAR diff =
    CALCULATE (
    DATEDIFF ( leastdate, maxdate, DAY ),
    ALLEXCEPT ( Table1, Table1[Account ID] )
    )
    RETURN
    diff + 0 & " Days"
     
     
    Please accept it as a solution if it matches your requirement🙂
    • KristofferAJ's avatar
      KristofferAJ
      Helper III
      4 years ago

      Hi @shwetadalal " I think I got it to work! - the formula you have made correctly takes the last date by country, but is is possible to have it fixed for type of fruit also?

       

      I don't think I was clear in my ask, but I need the last date per country per fruit. 

       

      When was the last date Orange was sold in Austria would be 10/9/2023 for both 'Konrad' and 'Pia' makes sense?

       

      So I need the formula to take both fruit and country into consideration

       

      Lastlay, anyway to make avoid blanks, it looks a bit weird in the cells where a date is missing

       

      Thanks

      Kristoffer

      • Anonymous's avatar
        Anonymous
        Not applicable
        4 years ago

        KristofferAJ Please make a small addition in the ALLEXCEPT() function in measure to take both fruit and country into consideration.

         

        DateDifference =
        VAR maxdate =
        CALCULATE (
        MAX ( Table1[LastSoldDate] ),
        ALLEXCEPT ( Table1, Table1[Country], Table1[Fruit] )
        )
        VAR leastdate =
        SELECTEDVALUE ( Table1[Dispatch] )
        VAR diff =
        CALCULATE (
        DATEDIFF ( leastdate, maxdate, DAY ),
        ALLEXCEPT ( Table1, Table1[Account ID] )
        )
        RETURN
        diff + 0 & " Days"
         
         
        Please accept it as a solution if it matches your requirement🙂

         

  • v-cazheng-msft's avatar
    v-cazheng-msft
    Community Support
    4 years ago

    Hi KristofferAJ ,

     

    May I know whether your problem has been resolved? If this post helps, then please consider Accept it as the solution to help the other members find it more quickly. Thanks a lot!

     

    Best Regards,

    Community Support Team _ Caiyun

  • KristofferAJ's avatar
    KristofferAJ
    Helper III
    4 years ago

    Hi Anonymous 

    I'm really enjoying your formula here... one thing I realize im missing is that if any of my date columns are blank the output will be very wrong.. (e.g. 45429)

     

    Can I restrict the output so it provides a blank output if 'Last sold date' OR 'Dispatch date' is blank

    Thanks in advance

    Kristoffer