Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

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
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.