Time between two dates with rules

[KristofferAJ]

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

Fruit	Account ID	Country	Dispath	First sold date	Last sold date
Pear	klara	Austria	10/23/2013	11/11/2013	2/10/2014
Orange	Konrad	Austria	7/18/2013	7/24/2013	8/5/2013
Apple	Mike	Austria	4/17/2013	4/25/2013	4/29/2013
Orange	Pia	Austria	8/7/2013	9/5/2013	10/9/2013
Pear	Eva	France	2/8/2014	3/17/2014	3/17/2014
Orange	John	France	8/28/2013	9/20/2013	9/20/2013
Apple	Shannon	France	5/29/2013	7/11/2013
Pear	Simon	France	8/20/2013	9/18/2013	12/11/2013
Pear	Chris	Germany	1/29/2014	3/3/2014	3/3/2014
Apple	Jim	Germany	5/3/2013	7/8/2013	7/8/2013
Pear	Liz	Germany	7/2/2013	7/29/2013	10/29/2013
Orange	Paul	Germany	8/13/2013	8/23/2013	8/23/2013
Apple	Beernard	Spain	5/29/2013	6/4/2013	7/24/2013
Pear	Karin	Spain		8/21/2013	12/20/2013
Orange	Susan	Spain	7/16/2013	7/22/2013	9/24/2013
+ 20 other	+ 1000 other	+ 20 other

[KristofferAJ]

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

[v-cazheng-msft]

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

[Anonymous]

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

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 · Accepted solution]

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