topic Re: Time between two dates with rules in DAX Commands and Tips

Time between two dates with rules

KristofferAJ — Thu, 17 Mar 2022 06:30:50 GMT

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

Re: Time between two dates with rules

Anonymous — Thu, 17 Mar 2022 07:35:32 GMT

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🙂

Re: Time between two dates with rules

KristofferAJ — Fri, 18 Mar 2022 15:29:21 GMT

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

Re: Time between two dates with rules

Anonymous — Mon, 21 Mar 2022 06:24:41 GMT

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🙂

Re: Time between two dates with rules

v-cazheng-msft — Thu, 24 Mar 2022 03:47:53 GMT

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

Re: Time between two dates with rules

KristofferAJ — Tue, 17 May 2022 12:52:42 GMT

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