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 hav...
  • 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🙂

     

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🙂