Calculate days by criteria with DAX

[MEDSEN]

Hello everyone, 

I have been having strugling trying to calculate the period an Item takes from receiving to shipping. 

in this example below I want to match the item number in Culumn "Item"( there is not specific item number lookup, the system numerically generate four identical item numbers for each of the four criterias in the Activity Type column) and calculate the period from column "Activity Date" by criteria from column " Activity Type".

Iem	Activity Type	Activity Date
22	Receive	1/15/2024
22	Adjust	1/16/1900
22	Adjust	1/16/2024
22	Ship	1/28/2024

[Anonymous · Accepted solution]

Hi @MEDSEN ,

According to your description, here are my steps you can follow as a solution.

(1) My test data is the same as yours.

(2) We can create a measure. 

Days Between = 
var receive = CALCULATE(MIN('Table'[Activity Date]), FILTER(ALL('Table'),'Table'[Activity Type] = "Receive" && 'Table'[Iem]=MAX('Table'[Iem])))
var ship = CALCULATE(MIN('Table'[Activity Date]), FILTER(ALL('Table'),'Table'[Activity Type] = "Ship" && 'Table'[Iem]=MAX('Table'[Iem])))

RETURN IF(SELECTEDVALUE('Table'[Activity Type]) = "Ship", DATEDIFF(receive, ship, DAY))

(3) Then the result is as follows.

If the above one can't help you get the desired result, please provide some sample data in your tables (exclude sensitive data) with Text format and your expected result with backend logic and special examples. It is better if you can share a simplified pbix file. Thank you.

Best Regards,

Neeko Tang

If this post  helps, then please consider Accept it as the solution  to help the other members find it more quickly. 

[MEDSEN]

Thank you, Neeko! This is great. I very much appraciate it! 

[MEDSEN]

Hello, Vicky.

Thank you so much for this measure. It opens the way to solve big part of the problem.

I was wondering i this measure would work when we have multiple "four identical numbers" in the smame column. 

What would be the best approach to this based on this scenario below:

Iem	Activity Type	Activity Date
22	Receive	1/15/2024
22	Adjust	1/16/1900
22	Adjust	1/16/2024
22	Ship	1/28/2024
58	Receive	2/1/2024
58	Adjust	2/4/2024
58	Adjust	2/5/2024
58	Ship	2/13/2024
102	Receive	3/5/2024
102	Adjust	3/10/2024
102	Adjust	3/12/2024
102	Ship	3/25/2024

[vicky_]

Days Between = 
var receive = CALCULATE(MIN('Table'[Activity Date]), 'Table'[Activity Type] = "Receive", ALLEXCEPT('Table', 'Table'[Iem]))
var ship = CALCULATE(MIN('Table'[Activity Date]), 'Table'[Activity Type] = "Ship", ALLEXCEPT('Table', 'Table'[Iem]))

RETURN IF(SELECTEDVALUE('Table'[Activity Type]) = "Ship", DATEDIFF(receive, ship, DAY))

Above is the formula for a DAX measure that i've come up with - basically, store the received and shipped dates as variables, and then return the days between if the type is ship.