Forum Discussion
Time left based on Matrix
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?
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/339586.
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
3 Replies
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/339586.
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 - __PreviousHi joshua1990
how does the source data look like?
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
