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 there, first time posting.
I'm trying to figure out at way to calculate the number of days between work orders based off the Asset Name.
Once I know how many days are between the Work Orders of the same Asset Name, I will then be finding out how many fall into certain time windows (7, 14, 30 days).
For example:
Work Order | Asset Name | Created Date |
00009 | 321498878-cD | 12/08/21 |
00008 | 321498878-cD | 11/14/21 |
00003 | 59867ADG6890 | 9/16/21 |
00004 | 59867ADG6890 | 9/18/21 |
00001 | 22235746-WA | 6/11/21 |
00002 | 6789098-WM | 7/12/21 |
00005 | M69764 | 10/10/21 |
00007 | M69764 | 10/16/21 |
00006 | 59867ADG6890 | 10/14/21 |
I've tried many things and tried to look up solutions but this is a very specific situation.
I'm sure that I'm over-complicating this, but some help would be greatly apprecitated.
Thank you ahead of time
Solved! Go to Solution.
@Mtins8 you can write a measure like this
HowManyDaysElapsedPerAsset =
VAR _currentlyVisible =
MAX ( 'Table 1'[Created Date] )
VAR _assetName =
MAX ( 'Table 1'[Asset Name] )
VAR _preceding =
CALCULATE (
MAX ( 'Table 1'[Created Date] ),
FILTER (
ALL ( 'Table 1' ),
'Table 1'[Created Date] < _currentlyVisible
&& 'Table 1'[Asset Name] = _assetName
)
)
RETURN
DATEDIFF ( _preceding, _currentlyVisible, DAY )
@Mtins8 what is the desired output of this dataset?
Were you hoping for this?
HowManyDaysElapsedPerAsset =
VAR _min =
CALCULATE (
MIN ( 'Table 1'[Created Date] ),
ALLEXCEPT ( 'Table 1', 'Table 1'[Asset Name] )
)
VAR _max =
CALCULATE (
MAX ( 'Table 1'[Created Date] ),
ALLEXCEPT ( 'Table 1', 'Table 1'[Asset Name] )
)
RETURN
DATEDIFF ( _min, _max, DAY )
This was super helpful!
That was perfectly on track with what I was looking for, there's only one piece that I should have been more clear on.
Is there a way to tweak this so the output is the number of days between each Asset Name?
For example, for the Asset Name in which there are 3 entries it just lists the number of days between the first and last, instead of possibly being the number of days between each entry.
In terms of output, the column is perfect but if what I just mentioned requires a differnt output then that's okay with me.
@Mtins8 you can write a measure like this
HowManyDaysElapsedPerAsset =
VAR _currentlyVisible =
MAX ( 'Table 1'[Created Date] )
VAR _assetName =
MAX ( 'Table 1'[Asset Name] )
VAR _preceding =
CALCULATE (
MAX ( 'Table 1'[Created Date] ),
FILTER (
ALL ( 'Table 1' ),
'Table 1'[Created Date] < _currentlyVisible
&& 'Table 1'[Asset Name] = _assetName
)
)
RETURN
DATEDIFF ( _preceding, _currentlyVisible, DAY )
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 | |
26 | |
19 | |
14 | |
10 |
User | Count |
---|---|
58 | |
50 | |
44 | |
19 | |
18 |