Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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,
Solved! Go to 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 ) )
)
Hi @VoltesDev
You can use
Duration = DATEDIFF ( MIN ('Log'[snapshotdate] ), MAX ('Log'[snapshotdate] ) + 1 , DAY )
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.
@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 ) )
)
Thank you very much. I've learn a lot from you.
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
47 | |
24 | |
20 | |
15 | |
12 |
User | Count |
---|---|
55 | |
48 | |
43 | |
19 | |
19 |