How DAX calculate process time (cycle time) from log file

[VoltesDev]

Hello guys,

When we have a log file that usually contain rows about some changes in our data with the time stamp on it, how are we going to calculate it with DAX for the change time ?

Here is what my meaning, I have this kind of table :

Name	SnapshotDate	StepName
New prospect A	3/21/2022 10:17	1-Proposal
New prospect A	3/21/2022 10:35	1-Proposal
New prospect A	3/21/2022 23:03	1-Proposal
New prospect A	3/22/2022 23:04	1-Proposal
New prospect A	3/23/2022 23:04	1-Proposal
New prospect A	3/24/2022 23:03	1-Proposal
New prospect A	3/25/2022 23:03	2-Feedback
New prospect A	3/26/2022 23:03	2-Feedback
New prospect A	3/28/2022 23:02	2-Feedback
New prospect A	3/29/2022 23:03	3-Followup
New prospect A	3/30/2022 23:03	3-Followup
New prospect A	3/31/2022 7:36	3-Followup
New prospect A	3/31/2022 23:03	3-Followup
New prospect A	4/1/2022 23:03	4-Negotiation
New prospect A	4/2/2022 23:03	4-Negotiation
New prospect A	4/3/2022 23:03	4-Negotiation
New prospect A	4/4/2022 23:03	4-Negotiation
New prospect A	4/5/2022 23:03	4-Negotiation
Project 1234	3/21/2022 10:17	1-Proposal
Project 1234	3/21/2022 10:35	1-Proposal
Project 1234	3/21/2022 23:03	1-Proposal
Project 1234	3/22/2022 23:04	1-Proposal
Project 1234	3/23/2022 23:04	2-Feedback
Project 1234	3/24/2022 23:03	2-Feedback
Project 1234	3/25/2022 23:03	2-Feedback

So I want to calculate the time it took for one project (say New prospect A) when it is on step "1-Proposal", then how much time it took for its next steps.

Probably the expected result could be a report look like this :

I included my sample PBIX for easier to understand the look of my data in here : https://app.box.com/s/h018q41t7i28bd7ulbk454u6b0vvwmiw

Hope anyone can guide me how to build this on DAX language.

Thanks,

[tamerj1]

Hi @VoltesDev 
You can use

Duration = DATEDIFF ( MIN ('Log'[snapshotdate] ), MAX ('Log'[snapshotdate] ) + 1 , DAY )

[VoltesDev]

Hi @tamerj1 

Thank you, this is great! Mind if I ask why the Total cannot be sum up ? I tried in my report also the same as your picture, the grand total for Rows and Column look like still filtered on some of the row. 

Thanks again.

[tamerj1 · Accepted solution]

@VoltesDev 
It can be. Sorry I missed that

Duration = 
SUMX ( 
    SUMMARIZE ( 'Log','Log'[name],'Log'[stepname] ),
    CALCULATE ( DATEDIFF ( MIN ('Log'[snapshotdate] ), MAX ('Log'[snapshotdate] ) + 1 , DAY ) )
)

[VoltesDev]

Thank you very much. I've learn a lot from you.