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! 🙂
Helper IThanks 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
Community Championhr_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
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
Hi Ole,
The 0s are likely to do with the data not having records that are current date.
I have another solution on a separate post that may be more closely aligned to what you are after...
Check out my most recent post on this topic: https://community.powerbi.com/t5/Desktop/Running-total-sum-with-a-current-hour-flag-column/m-p/2137877#M789200
If you want help, I can modify it to apply to your issue here. Alternatively, I will be super proud of you if you attempt to apply and modify it first, then let me know how it goes? Happy either way, but I do love to see people that want to learn through applying and adopting!
Either way, I will help as needed k! 🙂
- hr_tetra
Hi Theo,
I do have current dates, but still I do not get 1's before current time yesterday:
I will have a look at the other solution in the other thread too. Thanks for the help!
Best regards,
Ole
- TheoC
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 😀