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🙂

     

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