Time left based on Matrix

[joshua1990]

Hi all!

I have a table like this:

Work Order Nr	Step 1 - Start	Step 1 - End	Step 2 - Start	Step 2 - End
A100	01.07.2022
B100	01.07.2022	01.08.2022	01.09.2022
C101	01.08.2022	01.09.2022	02.09.2022	03.09.2022
D104	01.09.2022	04.09.2022

Per column I have the start and end date per Step for each Work Order.

Then I have a matrix that shows me the expected days left for each Step:

Step	Start	End
Step 1	30	25
Step 2	20	18
Step 3	15	10
Step 4	10	5

Now I would like to calculate the number of days left for each work order based on the last date tracked and the matrix. 

This should include the number or days that already orrured for the individual step.

How would you do that with DAX?

[Anonymous]

Hi @joshua1990,

Did Greg_Deckler 's suggestions help with your scenario? if that is the case, you can consider Kudo or accept his suggestions to help others who faced similar requirements.

If that also doesn't help, please share more detailed information to help us clarify your scenario to test.

How to Get Your Question Answered Quickly 

Regards,

Xiaoxin Sheng

[tamerj1]

Hi @joshua1990 

how does the source data look like?

[Greg_Deckler · Accepted solution]

@joshua1990 I would start by unpivoting your Step columns. Then the DAX will be far easier. You could use something like MTBF. See my article on Mean Time Between Failure (MTBF) which uses EARLIER: http://community.powerbi.com/t5/Community-Blog/Mean-Time-Between-Failure-MTBF-and-Power-BI/ba-p/3395....
The basic pattern is:
Column = 
  VAR __Current = [Value]
  VAR __PreviousDate = MAXX(FILTER('Table','Table'[Date] < EARLIER('Table'[Date])),[Date])

  VAR __Previous = MAXX(FILTER('Table',[Date]=__PreviousDate),[Value])
RETURN
  __Current - __Previous