cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Tommyvhod Frequent Visitor
Frequent Visitor

Measure to filter days between operations

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 IDFinish date
48097101. 10. 2019
48097353. 10. 2019
48097504. 10. 2019
48097704. 10. 2019
48097807. 10. 2019
48097909. 10. 2019
4809711011. 10. 2019
4809716511. 10. 2019
4809721013. 10. 2019
48104301. 10. 2019
48104351. 10. 2019
48104502. 10. 2019
48104705. 10. 2019
481041108. 10. 2019
4810413511. 10. 2019
4810418013. 10. 2019
4810419015. 10. 2019.

 

Thank You very much

1 ACCEPTED SOLUTION

Accepted Solutions
Highlighted
Nathaniel_C Super Contributor
Super Contributor

Re: Measure to filter days between operations

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

 

 

 

 

 

Elapsed days.PNG

 

View solution in original post

15 REPLIES 15
Highlighted
Nathaniel_C Super Contributor
Super Contributor

Re: Measure to filter days between operations

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

 

 

 

 

 

Elapsed days.PNG

 

View solution in original post

Tommyvhod Frequent Visitor
Frequent Visitor

Re: Measure to filter days between operations

@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.

 

 

Nathaniel_C Super Contributor
Super Contributor

Re: Measure to filter days between operations

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.Calculated column.PNG

 

Nathaniel_C Super Contributor
Super Contributor

Re: Measure to filter days between operations

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 ] ) )
)
Tommyvhod Frequent Visitor
Frequent Visitor

Re: Measure to filter days between operations

@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?

Tommyvhod Frequent Visitor
Frequent Visitor

Re: Measure to filter days between operations

@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.

 

Bez názvu.png

 

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

Nathaniel_C Super Contributor
Super Contributor

Re: Measure to filter days between operations

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

Tommyvhod Frequent Visitor
Frequent Visitor

Re: Measure to filter days between operations

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

IDOperationTransaction DateTime
11679003010. 7. 201905:51
11679003510. 7. 201905:51
11679005010. 7. 201908:37
11679007010. 7. 201908:38
11679008010. 7. 201908:54
11679009010. 7. 201910:07
116790011010. 7. 201910:19
116790019010. 7. 201921:00
116790021011. 7. 201909:32
116790031711. 7. 201915:10
116790032011. 7. 201917:14
116790032312. 7. 201919:20
116790033013. 7. 201907:41
116790033313. 7. 201912:40
116790034013. 7. 201912:40
116790034328. 8. 201909:53
116790035028. 8. 201909:53
116790035314. 10. 201900:50
116790036014. 10. 201901:58
116790036314. 10. 201909:52
116790037014. 10. 201910:40
116790040114. 10. 201916:40
116790040214. 10. 201917:36
116790041014. 10. 201919:40
11679203012. 7. 201911:49
11679203512. 7. 201911:49
11679205012. 7. 201914:23
11679207012. 7. 201916:27
11679208012. 7. 201917:38
11679209015. 7. 201906:43
116792011015. 7. 201913:10
116792019015. 7. 201917:42
116792021015. 7. 201920:50
116792031716. 7. 201902:03
116792032016. 7. 201908:50
116792032318. 7. 201908:23
116792033018. 7. 201910:23
116792033318. 7. 201916:29
116792034022. 7. 201917:44
116792034310. 9. 201910:34
116792035018. 9. 201908:39
116792038519. 9. 201912: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 ) 

Nathaniel_C Super Contributor
Super Contributor

Re: Measure to filter days between operations

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

timedate1.PNG

t


















timedate2.PNG

 

Helpful resources

Announcements
October 2019 Community Highlights

October 2019 Community Highlights

October was a busy month in the community. Read the recap article to learn about some of the events and content.

New Badges

Incoming: New and Improved Badges

Exciting news: We've given our badges an overhaul and added brand news ones.

Ask Amir Anything

Exclusive LIVE Community Event No. 2 – Ask Amir Anything

Next in our Triple A series: Ask Amir Netz questions about the latest updates, features and future.

Analytics in Azure virtual event

Analytics in Azure virtual event

Experience a limitless analytics service built to ingest, prep, manage, and serve data for immediate use in Power BI.

Users Online
Currently online: 179 members 1,773 guests
Please welcome our newest community members: