cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
hr_tetra
Helper I
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

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

Want to connect?www.linkedin.com/in/theoconias

View solution in original post

13 REPLIES 13
TheoC
Super User
Super User

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

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!

Want to connect?www.linkedin.com/in/theoconias

Thanks 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

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

Want to connect?www.linkedin.com/in/theoconias

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

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

Want to connect?www.linkedin.com/in/theoconias

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!

Want to connect?www.linkedin.com/in/theoconias

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.

hr_tetra_1-1634635222779.png

 

 

Best regards,

 

Ole

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

Want to connect?www.linkedin.com/in/theoconias

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

BTW @TheoC , what does the HOUR calculation do?

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!

Want to connect?www.linkedin.com/in/theoconias

Hi Theo,

 

I do have current dates, but still I do not get 1's before current time yesterday: 

hr_tetra_0-1634632964129.png

I will have a look at the other solution in the other thread too. Thanks for  the help!

 

Best regards,

 

Ole

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!

Want to connect?www.linkedin.com/in/theoconias

Helpful resources

Announcements
Carousel_PBI_Wave1

2023 Release Wave 1 Plans

Power BI release plans for 2023 release wave 1 describes all new features releasing from April 2023 through September 2023.

Power BI Summit Carousel 2

Global Power BI Training

Make sure you register today for the Power BI Summit 2023. Don't miss all of the great sessions and speakers!

BizApps LATAM 2023

Business Application LATAM Summit 2023

Join the biggest FREE Business Applications Event in LATAM this February.

Power Platform Bootcamp

Global Power Platform Bootcamp

In this bootcamp we will deep-dive into Microsoft’s Power Platform stack with hands-on sessions and labs, delivered to you by experts and community leaders.