Forum Discussion

hr_tetra's avatar
hr_tetra
Icon for Helper I rankHelper I
5 years ago
Solved

Compare current progress today with yesterday at the same time

Hi all,   I have a directquery report of the production progress during the day. I would like to add a measure/calc column which figures out what the progress was at the same time as NOW() for yest...
Need Help
  • TheoC's avatar
    TheoC
    5 years ago

    hr_tetra give the following a go as a Calculated Column to return records from "yesterday's" balance.  It will allocate a "1" if they do. From here, you can some all amounts in your 'values' column that have a 1 allocated for the prior day.

     

    CurTimevPrevDay = 

    VAR _PriorDayValue = IF ( 'Table'[Date and Hour Column] = TODAY () - 1 , 1 , 0 )
    VAR _LessThanCuTime = IF ( 'Table'[Date and Hour Column] <= NOW () -1 , 1 , 0 )
    VAR _ConverToHour = HOUR ( 'Table'[Date and Hour Column] )
    VAR _PriorDay = IF ( AND ( _PriorDayValue = 1 , _LessThanCuTime = 1 ) , 1 , 0 )

    RETURN

    _PriorDay

    Let me know if you need a formula for the sum of the values, however, it should be quite simple such as using a measure to sum the new column:

    SumPrevDay = CALCULATE ( SUM ( Table1[CurTimevPrevDay] ) , FILTER ( 'Table' , Table[CurTimevPrevDay] = 1 ) )


    Hope this helps! 🙂

hr_tetra's avatar
hr_tetra
Icon for Helper I rankHelper I
5 years ago

Thanks Theo,

 

You have a sharp mind! I tested it now, but it will not give me 1 values for yesterdays data before current time, unfortunately. It only spits out zeroes...

 

I do understand the method, except the _convertohour step. Could you explain what that does?

 

Best regards,

 

Ole

TheoC's avatar
TheoC
Icon for Community Champion rankCommunity Champion
5 years ago

hr_tetra apologies, I forgot to respond to you question on _ConvertToHour. It extracts the Hour from the [Date and Time] column.  It can be wrapped outside any Date / Time or Time field.

 

Let me know if you would like some examples.

 

Also, in terms of your output, can you send me some sample data and I can recreate the measures to your requirements k 😀