topic Re: Measure to filter days between operations in DAX Commands and Tips

Measure to filter days between operations

Tommyvhod — Tue, 15 Oct 2019 11:19:16 GMT

Hi guys

I am quite new to power BI and I am stuck with a report I would like to make.

I have a list of IDs from production. We have in production 50 operations each with different number (e.g-20-50-70-80 ...) after finishing a part on each operation a finish date is added for the given operation. Additional thing is that the order of these operations are not alwazs the same but the IDnumber of the operation is always ascending ( 20-30-50-80 or 20-30-70-80 or 20-70-90 .... )

What I would like to make is: for all the IDs know the days between each operation ( and of course the whole production time for one ID) in days.

E.g:

ID	Operation ID	Finish date
48097	10	1. 10. 2019
48097	35	3. 10. 2019
48097	50	4. 10. 2019
48097	70	4. 10. 2019
48097	80	7. 10. 2019
48097	90	9. 10. 2019
48097	110	11. 10. 2019
48097	165	11. 10. 2019
48097	210	13. 10. 2019
48104	30	1. 10. 2019
48104	35	1. 10. 2019
48104	50	2. 10. 2019
48104	70	5. 10. 2019
48104	110	8. 10. 2019
48104	135	11. 10. 2019
48104	180	13. 10. 2019
48104	190	15. 10. 2019.

Thank You very much

Re: Measure to filter days between operations

Nathaniel_C — Tue, 15 Oct 2019 18:36:44 GMT

Hi Tommyvhod ,

Try these two measures to get the result in the picture. My table name is Fin.
Let me know if you have any questions.

If this solves your issues, please mark it as the solution, so that others can find it easily. Kudos are nice too.
Nathaniel

Date diff = 
VAR _Time =
     ( Fin[Finish date] ) //captures current date


Var _maxLastTime = CALCULATE(MAX(Fin[Finish date]),FILTER(ALLEXCEPT(Fin,Fin[ID ]),Fin[Finish date]< _Time))

var _datedif =DATEDIFF(_maxLastTime,Fin[Finish date],day)

return
if(_datedif>0,_datedif,0)

==================

Date Dif per ID = CALCULATE(DATEDIFF(MIN(Fin[Finish date]),MAX(Fin[Finish date]),DAY),Filter(ALLEXCEPT(fin,Fin[ID ]),Fin[ID ])

Re: Measure to filter days between operations

Tommyvhod — Tue, 15 Oct 2019 19:28:36 GMT

Nathaniel_C Thank you for your answer. I was unable to add the first measure. I wanted to add as a new measure where the data is, but I was unable to write the VAR expression. Did I want to add the measure in the wrong place?

For the second measure I received the following error message:

A single value for column 'ID' in table 'QAD_Data' cannot be determined. This can happen when a measure formula refers to a column that contains many values without specifying an aggregation such as min, max, count, or sum to get a single result.

Re: Measure to filter days between operations

Nathaniel_C — Tue, 15 Oct 2019 19:55:22 GMT

Hi Tommyvhod ,

So you provided us with a table. I just added a calculated column to the table.

Is that what you are doing? Let's work with DateDiff first.

You should be able to copy and paste, and then change the names to your table.

Re: Measure to filter days between operations

Nathaniel_C — Tue, 15 Oct 2019 20:22:50 GMT

Hi Tommyvhod ,

Or as measures:

Date diff m =
VAR _Time =
    MAX ( Fin[Finish date] ) //captures current date
VAR _maxLastTime =
    CALCULATE (
        MAX ( Fin[Finish date] ),
        FILTER ( ALLEXCEPT ( Fin, Fin[ID ] ), Fin[Finish date] < _Time )
    )
VAR _datedif =
    DATEDIFF ( _maxLastTime, _Time, DAY )
RETURN
    IF ( _datedif > 0, _datedif, 0 )
==========================================

Date Dif per ID m =
CALCULATE (
    DATEDIFF ( MIN ( Fin[Finish date] ), MAX ( Fin[Finish date] ), DAY ),
    FILTER ( ALLEXCEPT ( fin, Fin[ID ] ), MAX ( Fin[ID ] ) )
)

Re: Measure to filter days between operations

Tommyvhod — Wed, 16 Oct 2019 08:01:38 GMT

Nathaniel_C I was confused becouse of the VAR _Time but now I know how that works.

The formula works , It is really a good job... wow . The only bug I noticed is that if 2 operations are finished on the same day, than both days shows the difference that day and the previous day. ( like in the sample table october 11 - 2operation 110 and 165 both shows 2-2 days becouse of the operation 90 which was 2 days before, but it should show 2 and 0 )

Any ideas how to solve that one?

Re: Measure to filter days between operations

Tommyvhod — Wed, 16 Oct 2019 10:53:10 GMT

Nathaniel_C I am playing with the measure. I noticed that in some cases the results is only 0. I double checked the raw data and there are differences between the dates and stil shows 0 as difference, but not all the time. Could that be becouse of the formula or maybe becouse of weak laptop ( 4gb ram - maybe unable to refresh all the data. )

Update - one of the slicer was the issue for the 0s.

~~Here you can see that some rows shows only 0~~. and where the second arrow is the 5 operations were done in one day and shows the difference 4 for all ( that is the date the operation before the 5 operation was finished ). It should show 4-0-0-0-0

Any help is appreciated

Re: Measure to filter days between operations

Nathaniel_C — Wed, 16 Oct 2019 22:17:06 GMT

Hi Tommyvhod ,

Would you do me a favor so that I might help you? Please add some data in Power Query to the source table (by clicking on the gear icon in the first step.) Then repost it so that I can look at it. Use data that would be similar to the data that is producing the errors. This way we can solve for the problem.

Thank you,

Nathaniel

Re: Measure to filter days between operations

Tommyvhod — Thu, 17 Oct 2019 09:02:11 GMT

The only issues are the duplicate values for operations on same day. I add another excel example with some operations on same day.

ID	Operation	Transaction Date	Time
1167900	30	10. 7. 2019	05:51
1167900	35	10. 7. 2019	05:51
1167900	50	10. 7. 2019	08:37
1167900	70	10. 7. 2019	08:38
1167900	80	10. 7. 2019	08:54
1167900	90	10. 7. 2019	10:07
1167900	110	10. 7. 2019	10:19
1167900	190	10. 7. 2019	21:00
1167900	210	11. 7. 2019	09:32
1167900	317	11. 7. 2019	15:10
1167900	320	11. 7. 2019	17:14
1167900	323	12. 7. 2019	19:20
1167900	330	13. 7. 2019	07:41
1167900	333	13. 7. 2019	12:40
1167900	340	13. 7. 2019	12:40
1167900	343	28. 8. 2019	09:53
1167900	350	28. 8. 2019	09:53
1167900	353	14. 10. 2019	00:50
1167900	360	14. 10. 2019	01:58
1167900	363	14. 10. 2019	09:52
1167900	370	14. 10. 2019	10:40
1167900	401	14. 10. 2019	16:40
1167900	402	14. 10. 2019	17:36
1167900	410	14. 10. 2019	19:40
1167920	30	12. 7. 2019	11:49
1167920	35	12. 7. 2019	11:49
1167920	50	12. 7. 2019	14:23
1167920	70	12. 7. 2019	16:27
1167920	80	12. 7. 2019	17:38
1167920	90	15. 7. 2019	06:43
1167920	110	15. 7. 2019	13:10
1167920	190	15. 7. 2019	17:42
1167920	210	15. 7. 2019	20:50
1167920	317	16. 7. 2019	02:03
1167920	320	16. 7. 2019	08:50
1167920	323	18. 7. 2019	08:23
1167920	330	18. 7. 2019	10:23
1167920	333	18. 7. 2019	16:29
1167920	340	22. 7. 2019	17:44
1167920	343	10. 9. 2019	10:34
1167920	350	18. 9. 2019	08:39
1167920	385	19. 9. 2019	12:59

I have a separate column with times as well. If it helps ( or maybe it would be better for me as well to track the hours between operations )

Re: Measure to filter days between operations

Nathaniel_C — Thu, 17 Oct 2019 20:49:09 GMT

Hi Tommyvhod ,

Ok thanks for the data. I translated the date, and then combined the date and time. See if this makes sense to you. The underlined shows that about 12 hours translates to .50. Do me a favor and look this over, point out the issues that you see.

Thanks,

Let me know if you have any questions.

If this solves your issues, please mark it as the solution, so that others can find it easily. Kudos are nice too.
Nathaniel

Re: Measure to filter days between operations

Tommyvhod — Fri, 18 Oct 2019 06:25:14 GMT

Its seems to be good. Did you change anything in the formula?

Re: Measure to filter days between operations

Nathaniel_C — Fri, 18 Oct 2019 13:36:24 GMT

Hi Tommyvhod ,

Only what you changed, which was adding the time to the date. Which most likely solved your problem of because now we are not solving for days only. Different granularity.

Thanks, it was fun to work on!

Nathaniel

Re: Measure to filter days between operations

Tommyvhod — Mon, 21 Oct 2019 12:04:21 GMT

Could I ask how did you merged the Time and date column together? I simply added a new column and added the formula Date + Time. I received a format that seems to be ok. but when changed the measure formula, pointing to the combined date, all my datediff numbers switched to 74.

Re: Measure to filter days between operations

Nathaniel_C — Mon, 21 Oct 2019 12:24:54 GMT

Hi Tommyvhod ,

Here is my pbix DATEDIFF

Go to Power Query, select both columns, select Merge Columns, with space for a delimiter, and then change type of the new column to Date Time.
Let me know if you have any questions.

If this solves your issues, please mark it as the solution, so that others can find it easily. Kudos are nice too.
Nathaniel

Re: Measure to filter days between operations

Tommyvhod — Tue, 22 Oct 2019 04:23:03 GMT

It will need some fine tuning from the raw data point of view, but the formula works. Thank You

Re: Measure to filter days between operations

Nathaniel_C — Tue, 22 Oct 2019 13:15:46 GMT

You are welcome!