Events in Progress - displaying items present between two dates in Power BI / Excel with Power Query
ฝัง
- เผยแพร่เมื่อ 28 ก.ย. 2024
- 🔽More Info🔽
If you have a start date and end date but need to calculate how many people or items were present between those dates then you can use Power Query to help with this
This approach will work in Power BI and Excel
Source CSV file and Power BI solution is here
aasolutions.sh...
I've also done a video using DAX to solve this.
• DAX and the Start Date...
You may find the Power Query method means your data takes longer to refresh but there is likely an upside in the speed of your interactions between visuals in the report for your end users.
Find me on various social media platforms
wyn.bio.link/
![[🔴LIVE] งานประกาศรางวัล MAYA TV AWARDS 2024 : มายามหาชน](http://i.ytimg.com/vi/nwmWYXXVwBY/mqdefault.jpg)
I've had similar issue with a price lists where each price is valid from-to date and I had to convert the two columns into a day-by-date table. Pitty I haven't seen this video earlier, it would save me a few hours. ;) All your videos are just great. I love them. Thank you, Wyn!
Great, thanks for letting me know my videos are helpful Jakub. Greatly appreciated.
Minor suggestion for creating date lists:
CreateOccupancyDateLists = Table.AddColumn(Source, "OccupancyDates", each {Number.From([CheckIn])..Number.From([CheckOut])}),
ExpandDateLists = Table.ExpandListColumn(CreateOccupancyDateLists, "OccupancyDates"),
SetTypeToDate = Table.TransformColumnTypes(ExpandDateLists,{{"OccupancyDates", type date}})
Nice technique Ron
Super useful and clear explenations. Thank you much !
You’re welcome. Thanks for taking the time to leave a kind comment
Looks good, I will test it out. Seems like last weeks Dax might be a little easier, especially if you are working with large tables with big date ranges
It depends, the refresh will take longer but the visual interaction / page load could be quicker
I like to get this sort of calculation done as close to the data as possible, so Power Query would be my first choice.
@@zzota depends on the usercase. I have 500,000 rows of data, with an average of 6 months between the start and end date or each row. Seems like dax would be the way to go, rather than creating millions of extra rows with power query. 9 times out of 10 I’d rather use PQ too but Dax might be the better option for me this time
Check this out dobbsondata.co.uk/2022/02/18/data-model-vs-dax/
Thank you Wyn. Another great resolution!
Thanks as always Iván
thanks wyn this video helps me a lot to fix one of my client issue 😊
Great. I appreciate you taking the time to let me know you found it useful
@@AccessAnalytic If one day you come in Paris, will be a pleasure to meet in person :)
Awesome Video, thanks a lot! This would really help me out and save me lots of time.
I appreciate you taking the time to let me know you found it useful
A great example, however I needed the dates in between to skip weekends and holidays. Networkdays does that but is a DAX function. People have written M language equivalents as custom functions but its beyond my skill level to implement them:(
I’d merge in a table of holidays, then add conditional column called ISWorkingDay, saying if day number of week is 6 or 7 or is holiday then 0 else 1
Then filter the table to only include 1s
@@AccessAnalytic Ok, I've done that, and filtered my date table down to only the working days. In my other table I have the list of employees together with their sick start days , end dates and hours sick that I want to spread evenly across the working days, so I can eventually add up all the sickleave hours per working day. I've tried creating a measure that includes a filter, so that for every staff member I filter to see if each date is in the range, and a working day, and if so I SUMX it, but I haven't had much success! I think I'm struggling with the syntax, but I could be going about it totally incorrectly. Any help would be appreciated.
Ah, sorry, merge your holidays onto your fact table after expanding your date list and add the IsWorkingDay there.
If you want to go down the DAX path then there’s this option th-cam.com/video/YL7H1Rqckb0/w-d-xo.htmlsi=_Kw7ofWKGuglZ5lz
Thank you for the video!! How would you do this if you wanted to look at number of occupants for each month?
I think that's what my chart was showing at 10:30, I then also added date
Really helpful video. I would like to use this type of method to find out how many employees I have on shift for every 15 min block of time. Obviously this video is for days. Is there a way to do it for 15 min time intervals?
Maybe this approach and your need to incorporate a time table instead of a date table
DAX and the Start Date End Date Problem aka Events In Progress
th-cam.com/video/YL7H1Rqckb0/w-d-xo.html
Time table; th-cam.com/video/-q7v56p192M/w-d-xo.htmlsi=y4P7MZ4BQK1q6em_
Very useful!
Cheers
Thank you, exactly what I needed. :)
I appreciate you taking the time to let me know you found it useful
I think this could be very useful in an aircraft maintenance scheduling scenario where I'm trying to determine the mutual overlap of Aircraft and Mechanics per hour
I have arrivals and departures of Aircraft and I have start and stops for Mechanics Shifts
Good stuff
Looking at some comments, would list functions and group by break query folding - if not you could employ this method let the database handle the transformation and return a table of distinct dates with the sum and count of guests and rooms simplifying the dax needed and improving powerquery performance.
I’m not sure Glyn, I rarely deal with SQL databases, I’m sure the ideal scenario is to build a view with the data you need
@@AccessAnalytic I think it depends on the information that is going to be presented, for example, an age of delinquent customer balances
very useful, thanks, is it possible to do it by month or year instead of days?
Yep, you could select the end date and start date columns and Add Column - Subtraction, then right click convert to total years/ months. And use that as a reference in your formula
Perfect! absolutely what I needed, thank you so much
Awesome!
You are awesome!! Thanks.
Too kind Pragnesh 😁
Cool approach
Thanks Jonathan
Question: shouldn’t you add 1 to the duration to include the day of check out?
If you start from big data, extending a table like this may become huge. I would try not to increase the cardinality like that.
My first truly big data challenge some 4 years ago was of exactly this kind: the grain was minutes in a day and the cardinality was millions.
I solved it in DAX where I also did an extra step of turning the results into a histogram on a day-basis, meaning: calculating the max presence per day and then counting the number of days per presence-bucket in a histogram. That way I calculated the daily occupancy over -say- a year, and we could see how many days a certain occupancy was reached.
Interesting Geert, I think there’s use cases for both approaches and the specific scenario will influence the decision
I don’t think I’d add the checkout date as the person is not there that day
Also, it wil depend on what other data you include in the expanded table. A single column with low cardinality should compress well
@@AccessAnalytic Wyn, as per your 3rd comment, I may have the wrong definition of ‘cardinality’, the way I learned it it’s the number of records in a table. Do you mean to say that it’s more like the number of distinct records?
Hi
Thank you for the video. How to solve the problem with datetime, not Date. What if the difference between the start date and the end date is less than 24 hours( 01/01/2023 00:00:00 to 1/1/2023 23:59:59? If you change it to Date, then start_date = end_date.
Possibly add some sort of IF statement to capture that scenario
Nice video. Could you please post the file too?
Sure, thanks for the reminder. I've added it in the description and it's here too aasolutions.sharepoint.com/:f:/s/PubliclyAvailableContent/Emq-INXt7R5Cq_L4nHzJD-gBVhueMdTHm7zFpbiifPccOw?e=ySNPwi
really good, it was what I needed, just did a little change to months instead days
Great, thanks for letting me know
Hello, how did you do that?
Great work - can expand it for hour slots / 15 min slot etc thru rounding in PQ.
Also if dax concaternatex is used we can find the name of people or any text values be shown like Mr XXX appears in the room number 211 for 30th and 31st of the month and alike. Thanks
Thanks
It’s just awesome!
So happy I found your videos!
*running to check if it helps me to calculate cargo storage cost for each parcel for each day, since parcels quantity, pieces and weight changes from day to day*
Hope it helps
Brillant! Thanks for posting! I do agree doing this in PQ may be more efficient than DAX.
Thanks Tom
How to know the movement out/in from hotel to another? When I have start and end dates. The problem is that if someone has an end date with hotel X and Start date with the same hotel X, I don't want to count it as checked out
I don’t quite understand why someone would have a checkout and then checkin with same hotel. But maybe need to fi some sort of Group By in Power Query and add a flag to exclude certain scenarios
Another great real-world example with a great solution.
Thanks
Possibly the most helpful thing you have shared. Thank you.
Say you wanted to apportion the bill or cost by day. Would you perhaps:
- calculate the service period in days (difference between start and end)
- calculate the average cost per day (cost divided by service period)
- delete columns as per your example.
- then you can have a measure calculating the cost over the shown date range.
Cheers. I think If you have a measure for Cost then you can divide it by COUNTROWS of your expanded events in progress table.
@@AccessAnalytic that is even better. Thanks.
No worries
I can't do that in my data. it does not show the right results
That’s a shame. Maybe you can post more details to one of the forums such as
techcommunity.microsoft.com/t5/microsoft-excel/ct-p/Excel_Cat?WT.mc_id=M365-MVP-5002589
community.powerbi.com/t5/Desktop/bd-p/power-bi-designer
www.reddit.com/r/PowerBI/s/QnQZVihBnP
Really Very useful, Thank You
You’re welcome Vishal
Hey in the DAX video you showed the monthly version after the daily solution, do you have one for power query?
I think you could just display the daily PQ version at a monthly level if connected to a Calendar table.
Are you wanting to show the number of people present during the month, or the number of people there at Month End?
Hi Wyn, I just came here from your DAX video on this topic. Very helpful and certainly a different approach. I think the use case is best for those events which have a fairly short duration as in your example. In my case I have some open issues that are over a year old, so the table would expand massively.
In your DAX example, you account for those events still in progress, i.e. events without an EndDate. For PQ would you add another condition when there is no EndDate, perhaps using Date.From(DateTime.LocalNow()) as the EndDate?
Good point on the end date, some sort of approach like you suggest sounds sensible way to go.