Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
4 years ago
Solved

Calculating Difference between Two Days based on a variable

Good Day   I need assistance with adding a Calulcated Column to my Database.   I need worked hours to take the SMR (Hour Meter) from the day it is recorded and subtract it from the SMR of that "s...
Need Help
  • v-chenwuz-msft's avatar
    v-chenwuz-msft
    4 years ago

    Hi Anonymous ,

     

    You can use VAR to improve preformance.

    Previous SMR =
VAR _t =
    TOPN (
        1,
        FILTER (
            DailyProduction,
            DailyProduction[EIID] = EARLIER ( DailyProduction[EIID] )
                && DailyProduction[SMR] < EARLIER ( DailyProduction[SMR] )
        ),
        [Date], DESC
    )
RETURN
    MAXX ( _t, [SMR] )

     

     

    Best Regards

    Community Support Team _ chenwu zhu

     

    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Anonymous's avatar
Anonymous
Not applicable
4 years ago

Just to expand, I woudl essentally want the 6911 to rather be the result of the 16298-16294. Then the 2nd line would need to subtract 16294 from the lowest in the shift which would be 16293.

 

Would it be easier to use a function that sorts the data before calculating?

 

 

v-chenwuz-msft's avatar
v-chenwuz-msft
Community Support
4 years ago

Hi Anonymous ,

 

Don't quite understand your explanation. Can you give simple data and include the expected output values? It is best to share it in a tabular format.

 

Or you can try this expression:

Column =
VAR _last =
    TOPN (
        1,
        FILTER (
            'Table',
            [EIID] = EARLIER ( 'Table'[EIID] )
                && [date&shift] < EARLIER ( 'Table'[date&shift] )
        ),
        [date&shift], DESC
    )
RETURN
    IF ( COUNTROWS ( _last ) > 0, [Shift] - MAXX ( _last, [Shift] ) )

 

Result:

Pbix in the end you can refer.

Best Regards

Community Support Team _ chenwu zhu

 

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

  • Anonymous's avatar
    Anonymous
    Not applicable
    4 years ago
    Hi, that formula doesn't seem to be working, it just generates "-1" in all the columns.
     
    I have managed to come up with a formula that works. The only issue is that that data set is over 1m lines, and processing this column takes almost all my memory and 10 minutes to run.
     
    Previous SMR =
    var _EIID = DailyProduction[EIID]
    var _SMR = DailyProduction[SMR]
    return
    CALCULATE(
    MAX(DailyProduction[SMR]),
    TOPN(1, FILTER(DailyProduction, DailyProduction[EIID] = _EIID && DailyProduction[SMR]<_SMR), DailyProduction[Date], DESC)
    )
     

     

    • v-chenwuz-msft's avatar
      v-chenwuz-msft
      Community Support
      4 years ago

      Hi Anonymous ,

       

      You can use VAR to improve preformance.

      Previous SMR =
VAR _t =
    TOPN (
        1,
        FILTER (
            DailyProduction,
            DailyProduction[EIID] = EARLIER ( DailyProduction[EIID] )
                && DailyProduction[SMR] < EARLIER ( DailyProduction[SMR] )
        ),
        [Date], DESC
    )
RETURN
    MAXX ( _t, [SMR] )

       

       

      Best Regards

      Community Support Team _ chenwu zhu

       

      If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

      • Anonymous's avatar
        Anonymous
        Not applicable
        4 years ago

        Thanks a lot! It still takes a while to run but I think that is just the nature of the calculation I am doing. I am changing the actual input of the database to include this number already so that I don't have to calculate it via formula.

         

        Thanks again