topic Re: Calculating number of days between work orders in DAX Commands and Tips

Calculating number of days between work orders

Anonymous — Wed, 29 Dec 2021 23:19:57 GMT

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

Re: Calculating number of days between work orders

smpa01 — Wed, 29 Dec 2021 23:36:42 GMT

Anonymous 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 )

Re: Calculating number of days between work orders

Anonymous — Thu, 30 Dec 2021 00:17:34 GMT

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.

Re: Calculating number of days between work orders

smpa01 — Thu, 30 Dec 2021 00:50:28 GMT

Anonymous 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 )