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

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

     

     

     

     

     

     

15 Replies

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

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

        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.

         

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

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