Get certified in Microsoft Fabric—for free! For a limited time, the Microsoft Fabric Community team will be offering free DP-600 exam vouchers. Prepare now
Hi all, been searching the forums for an answer for a calculation I need and so far have come up blank...
I've looked through the following threads (as these seem to have solved a similar 'problem') but the solutions don't appear to work for me - or I can't get them to work correctly?
https://community.powerbi.com/t5/Desktop/DATEDIFF-between-Rows-and-GROUP-BY/td-p/309994 - this is seemingly a very similar problem as I'm facing and while the measure works, the values I get are not as expected.
Essentially, I have rows which are distinguished by a unique id "SessionID" and the number of rows for each SessionID isn't fixed.
I'm trying to formulate a DATEDIFF(?) output which looks at the highest and lowest value in the date/time column for each distinct SessionID and returns the difference. So for SessionID 2 below, I'd like an output returned of 7 secs - for SessionID 3 > 204 secs and so on...
I'm new to DAX and each time I think I'm close with the expression I need, it just doesn't work... please send help!
SessionID | Audit Date |
2 | 06/09/2017 13:46:14 |
2 | 06/09/2017 13:46:20 |
2 | 06/09/2017 13:46:20 |
2 | 06/09/2017 13:46:21 |
3 | 06/09/2017 13:49:01 |
3 | 06/09/2017 13:51:10 |
3 | 06/09/2017 13:52:00 |
3 | 06/09/2017 13:52:24 |
3 | 06/09/2017 13:52:25 |
4 | 06/09/2017 13:54:26 |
4 | 06/09/2017 13:54:32 |
4 | 06/09/2017 13:54:36 |
4 | 06/09/2017 13:54:36 |
4 | 06/09/2017 13:54:36 |
4 | 06/09/2017 13:54:57 |
4 | 06/09/2017 13:55:06 |
4 | 06/09/2017 13:55:06 |
5 | 06/09/2017 13:55:23 |
5 | 06/09/2017 13:55:30 |
5 | 06/09/2017 13:55:50 |
5 | 06/09/2017 13:55:52 |
5 | 06/09/2017 13:56:09 |
5 | 06/09/2017 13:56:09 |
5 | 06/09/2017 13:56:10 |
5 | 06/09/2017 13:56:11 |
5 | 06/09/2017 13:56:11 |
Thanks in advance,
Daniel
Solved! Go to Solution.
Hi @Anonymous
If you create 2 measures that max and min the Audit Date. then create a 3rd measure that does a datediff of these 2 measures.
MinAudit = CALCULATE(min(Table10[Audit Date])) MaxAudit = CALCULATE(max(Table10[Audit Date])) Difference = DATEDIFF([MinAudit], [MAxAudit],SECOND)
Then add them to a matrix with SessionId on Rows it should give you what you need.
Hi @Anonymous
If you create 2 measures that max and min the Audit Date. then create a 3rd measure that does a datediff of these 2 measures.
MinAudit = CALCULATE(min(Table10[Audit Date])) MaxAudit = CALCULATE(max(Table10[Audit Date])) Difference = DATEDIFF([MinAudit], [MAxAudit],SECOND)
Then add them to a matrix with SessionId on Rows it should give you what you need.
Hi @gooranga1 thanks for this, this did indeed work. Would you (or anyone) know of a way of combining the measures to create a single measure? Rather than having 3 'bouncing' off of each other...
Either way, this does what I need for now - would welcome any alternative solutions anyone may have.
ATB, Daniel
If using @gooranga1 method, just create the MinAudit and MaxAudit as a variable
var MinAudit = CALCULATE(min(Table10[Audit Date])) var MaxAudit = CALCULATE(max(Table10[Audit Date]))
RETURN Difference = DATEDIFF(MinAudit, MaxAudit,SECOND)
@Anonymous,
or if you want one measure you can just use,
Difference 1 = DATEDIFF(CALCULATE(min(Table10[Audit Date])), CALCULATE(max(Table10[Audit Date])),SECOND)
@Anonymous how would your method be used inside power bi desktop? Where does that code get pasted?
@gooranga1 All in the DAX expression just like you are doing there, but instead
Difference 1 =
var MinAudit = CALCULATE(min(Table10[Audit Date])) var MaxAudit = CALCULATE(max(Table10[Audit Date]))
RETURN Difference = DATEDIFF(MinAudit, MaxAudit,SECOND)
Thanks (again) to both @Anonymous and @gooranga1 for the additional responses! Greatly appreciated!
One thing I noticed is that if I use the example from @Anonymous I have to remove (after RETURN) " Difference = " as this seems to break the expression.
So I end up with the below (for anyone else looking)
Time in seconds = var MINaudit = CALCULATE(min('tablename'[AuditDate])) var MAXaudit = CALCULATE(max('tablename'[AuditDate])) RETURN DATEDIFF(MinAudit, MaxAudit,SECOND)
ATB, Daniel
I have not done this specifically, but my approach would be to use MINX with a Filter on the SessionID.. Something like the following..
MINX(KEEPFILTERS(VALUES('Data'[WW])), CALCULATE(MIN('Data'[Date])))
You could then do the same with MAXX and use the output of those to in order to get the Time Difference.
Check out the October 2024 Power BI update to learn about new features.
Learn from experts, get hands-on experience, and win awesome prizes.
User | Count |
---|---|
115 | |
112 | |
105 | |
95 | |
58 |
User | Count |
---|---|
174 | |
147 | |
136 | |
102 | |
82 |