Forum Discussion
hr_tetra
Helper I
Helper ICompare 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...
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
_PriorDayLet 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! 🙂
TheoC
Community Champion
Community ChampionThat is awesome news hr_tetra! Well done, Ole!
Apologies, I forgot to respond to you question on _ConvertToHour earlier. It extracts the Hour from the [Date and Time] column. It can be wrapped outside any Date / Time or Time field.
I am really glad it all worked my friend. Well done!