cancel
Showing results for
Did you mean:
Helper I

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 yesterday. Does anybody have a solution for this problem?

Status today current time = 5000 units (have this)

Status yesterday at current time = 4000 units (want to calculate this)

The point is to see if we are in front of or behind compared to the day before were we produced x units. Another variant would be to find the average units produced for the last week at this time of day, compared to today.

Best regards,

Ole

1 ACCEPTED SOLUTION
Super User

@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! 🙂

If I have posted a response that resolves your question, please accept it as a solution to formally close the post.

Also, if you are as passionate about Power BI, DAX and data as I am, please feel free to reach out if you have any questions, queries, or if you simply want to connect and talk to another data geek!

13 REPLIES 13
Super User

You can use PREVIOUSDAY function to achieve this.

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

If I have posted a response that resolves your question, please accept it as a solution to formally close the post.

Also, if you are as passionate about Power BI, DAX and data as I am, please feel free to reach out if you have any questions, queries, or if you simply want to connect and talk to another data geek!

Helper I

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

Super User

@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! 🙂

If I have posted a response that resolves your question, please accept it as a solution to formally close the post.

Also, if you are as passionate about Power BI, DAX and data as I am, please feel free to reach out if you have any questions, queries, or if you simply want to connect and talk to another data geek!

Helper I

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

Super User

@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 😀

If I have posted a response that resolves your question, please accept it as a solution to formally close the post.

Also, if you are as passionate about Power BI, DAX and data as I am, please feel free to reach out if you have any questions, queries, or if you simply want to connect and talk to another data geek!

Super User

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/21378...

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! 🙂

If I have posted a response that resolves your question, please accept it as a solution to formally close the post.

Also, if you are as passionate about Power BI, DAX and data as I am, please feel free to reach out if you have any questions, queries, or if you simply want to connect and talk to another data geek!

Helper I

OK, so I have been troubleshooting. I do get values from the _LessThanCuTime if I return that. If I return _PriorDayValue however, I only get 0's, so that is why I only get 0's in the end.

If I change the _PriorDayValue from column reference from the datetime column to a date column I get values. Will check if the values I get makes sense now.

There is a 4 hour mismatch in the date/datetime caused by GMT, but it looks like this works (as soon as I correct for this). The server with the original data is down right now, but will apply it there as soon as its back.

Best regards,

Ole

Super User

@hr_tetra that is an brilliant troubleshooting my friend! Keep me posted!

If I have posted a response that resolves your question, please accept it as a solution to formally close the post.

Also, if you are as passionate about Power BI, DAX and data as I am, please feel free to reach out if you have any questions, queries, or if you simply want to connect and talk to another data geek!

Helper I

Kudos to you, Theo!

I just went over the numbers and this solution works. I only changed from datetime to date in the _PriorDayValue.

Thanks a lot for the help, much appreciated!

Kind regards,

Ole

Helper I

BTW @TheoC , what does the HOUR calculation do?

Super User

That 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!

If I have posted a response that resolves your question, please accept it as a solution to formally close the post.

Also, if you are as passionate about Power BI, DAX and data as I am, please feel free to reach out if you have any questions, queries, or if you simply want to connect and talk to another data geek!

Helper I

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

Super User

Hi @hr_tetra, did it provide what you wanted?

If so, please let me know by marking it as a solution.  If not, let me know how I can better help you achieve the outcome you're after 🙂

If I have posted a response that resolves your question, please accept it as a solution to formally close the post.

Also, if you are as passionate about Power BI, DAX and data as I am, please feel free to reach out if you have any questions, queries, or if you simply want to connect and talk to another data geek!

Announcements

Power BI T-Shirt Design Challenge 2023

Vote for your favorite t-shirt design now through March 28.

Power BI March 2023 Update

Find out more about the March 2023 update.

March 2023 Events

Find out more about the online and in person events happening in March!

Top Solution Authors
Top Kudoed Authors