Events in Progress - displaying items present between two dates in Power BI / Excel with Power Query 

Thanks

Great, thanks for letting me know my videos are helpful Jakub. Greatly appreciated.

Thanks as always Iván

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 :)

Thanks Tom

You’re welcome. Thanks for taking the time to leave a kind comment

Nice technique Ron

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

Hope it helps

Great, thanks for letting me know

Hello, how did you do that?

I appreciate you taking the time to let me know you found it useful

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 ru-vid.com/video/%D0%B2%D0%B8%D0%B4%D0%B5%D0%BE-YL7H1Rqckb0.htmlsi=_Kw7ofWKGuglZ5lz

I appreciate you taking the time to let me know you found it useful

Too kind Pragnesh 😁

You’re welcome Vishal

Thanks Jonathan

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/

Thanks

Good stuff

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 ru-vid.com/video/%D0%B2%D0%B8%D0%B4%D0%B5%D0%BE-YL7H1Rqckb0.html Time table; ru-vid.com/video/%D0%B2%D0%B8%D0%B4%D0%B5%D0%BE--q7v56p192M.htmlsi=y4P7MZ4BQK1q6em_

I think that's what my chart was showing at 10:30, I then also added date

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

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

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

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?

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

Possibly add some sort of IF statement to capture that scenario

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?

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

Good point on the end date, some sort of approach like you suggest sounds sensible way to go.