Starting December 3, join live sessions with database experts and the Microsoft product team to learn just how easy it is to get started
Learn moreGet certified in Microsoft Fabric—for free! For a limited time, get a free DP-600 exam voucher to use by the end of 2024. Register now
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
Solved! Go to 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
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.
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
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:
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
Starting December 3, join live sessions with database experts and the Fabric product team to learn just how easy it is to get started.
March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Early Bird pricing ends December 9th.
User | Count |
---|---|
93 | |
87 | |
84 | |
67 | |
49 |
User | Count |
---|---|
141 | |
114 | |
111 | |
59 | |
59 |