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.
Hi all,
I am looking for some help calculating the time difference between 2 dates for values at different intervals throughout my data. Sample of the data is below.
Example: I need to find the time duration between User ABC = License Count 1 and User ABC = License Count -1, time duration between User XYZ = License Count 1 and User XYZ = License Count -1.
As you can see from the User ADE, there can be some number of rows between a license count = 1 and = -1 for the same user.
Any help gladly appreciated, thank you.
Timestamp (UTC) | File Acquired | License Count | User |
18/12/2017 0:01 | Acquired | 1 | ABC |
18/12/2017 0:13 | Acquired | 1 | XYZ |
18/12/2017 0:20 | Released | -1 | ABC |
18/12/2017 0:31 | Acquired | 1 | ADE |
18/12/2017 0:31 | Released | -1 | XYZ |
18/12/2017 0:37 | Acquired | 1 | FBA |
18/12/2017 0:38 | Released | -1 | FBA |
18/12/2017 0:41 | Released | -1 | NRA |
18/12/2017 0:41 | Acquired | 1 | NRA |
18/12/2017 0:52 | Released | -1 | ADE |
Solved! Go to Solution.
Hi @gunner
Here it goes
Time Difference = VAR TimeAtAcquisition = CALCULATE ( FIRSTNONBLANK ( 'LicenseUsage'[Timestamp (UTC)], 1 ), FILTER ( ALLEXCEPT ( 'LicenseUsage', 'LicenseUsage'[User] ), 'LicenseUsage'[Action] = 1 ) ) VAR TimeAtRelease = CALCULATE ( FIRSTNONBLANK ( 'LicenseUsage'[Timestamp (UTC)], 1 ), FILTER ( ALLEXCEPT ( 'LicenseUsage', 'LicenseUsage'[User] ), 'LicenseUsage'[Action] = -1 ) ) RETURN DATEDIFF ( TimeAtAcquisition, TimeAtRelease, MINUTE )
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 |
---|---|
111 | |
100 | |
80 | |
64 | |
57 |
User | Count |
---|---|
146 | |
110 | |
93 | |
84 | |
67 |