Forum Discussion

Tommyvhod's avatar
Tommyvhod
Icon for Helper II rankHelper II
6 years ago
Solved

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...
  • Nathaniel_C's avatar
    Nathaniel_C
    6 years ago

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

     

     

     

     

     

     

Tommyvhod's avatar
Tommyvhod
Icon for Helper II rankHelper II
6 years ago

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's avatar
Nathaniel_C
Icon for Community Champion rankCommunity Champion
6 years ago

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

t


















 

  • Nathaniel_C's avatar
    Nathaniel_C
    Icon for Community Champion rankCommunity Champion
    6 years ago

    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

     

  • Tommyvhod's avatar
    Tommyvhod
    Icon for Helper II rankHelper II
    6 years ago

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

  • Nathaniel_C's avatar
    Nathaniel_C
    Icon for Community Champion rankCommunity Champion
    6 years ago

    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

  • Tommyvhod's avatar
    Tommyvhod
    Icon for Helper II rankHelper II
    6 years ago

    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.