Forum Discussion
Helper ICompare current progress today with yesterday at the same time
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! 🙂
Community ChampionHi hr_tetra
You can use PREVIOUSDAY function to achieve this.
Here is a link: https://docs.microsoft.com/en-us/dax/previousday-function-dax
Once you have created a measure for Previous Day, you can then create a second measure being Current Day v Previous Day to calculate and present the variance between both (e.g. Variance Measure = [Current Day] - [Previous Day] ).
Hope it helps 🙂
Theo
hr_tetraThanks for the reply, Theo!
Does PREVIOUSDAY() work for times as well? When I try this with the EventTime column I get an error, saying there are several dates. Using MAX(EventTime) within PREVIOUSDATE() does not do the trick.
What I want is not compare today with yesterday, , I want to compare the progress so far today with the progress at the same time yesterday, not the total for yesterday.
Best regards,
Ole
- TheoC
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! 🙂