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!

@AccessAnalytic

2 жыл бұрын

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

Another great real-world example with a great solution.

@AccessAnalytic

Жыл бұрын

Thanks

Thank you Wyn. Another great resolution!

@AccessAnalytic

2 жыл бұрын

Thanks as always Iván

Super useful and clear explenations. Thank you much !

@AccessAnalytic

6 ай бұрын

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

Brillant! Thanks for posting! I do agree doing this in PQ may be more efficient than DAX.

@AccessAnalytic

2 жыл бұрын

Thanks Tom

thanks wyn this video helps me a lot to fix one of my client issue 😊

@AccessAnalytic

3 ай бұрын

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

@neo1tom

3 ай бұрын

@@AccessAnalytic If one day you come in Paris, will be a pleasure to meet in person :)

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*

@AccessAnalytic

2 жыл бұрын

Hope it helps

really good, it was what I needed, just did a little change to months instead days

@AccessAnalytic

Жыл бұрын

Great, thanks for letting me know

@monserratabarca5563

5 ай бұрын

Hello, how did you do that?

Really Very useful, Thank You

@AccessAnalytic

2 жыл бұрын

You’re welcome Vishal

Awesome Video, thanks a lot! This would really help me out and save me lots of time.

@AccessAnalytic

9 ай бұрын

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

You are awesome!! Thanks.

@AccessAnalytic

2 жыл бұрын

Too kind Pragnesh 😁

Thank you, exactly what I needed. :)

@AccessAnalytic

4 ай бұрын

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

Cool approach

@AccessAnalytic

2 жыл бұрын

Thanks Jonathan

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

@AccessAnalytic

2 жыл бұрын

Nice technique Ron

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.

@AccessAnalytic

Жыл бұрын

Cheers. I think If you have a measure for Cost then you can divide it by COUNTROWS of your expanded events in progress table.

@HandyPaladin

Жыл бұрын

@@AccessAnalytic that is even better. Thanks.

@AccessAnalytic

Жыл бұрын

No worries

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

@AccessAnalytic

2 ай бұрын

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

@walterstevens8676

2 ай бұрын

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

@AccessAnalytic

2 ай бұрын

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 kzread.info/dash/bejne/i4BrqpOLocbRkpM.htmlsi=_Kw7ofWKGuglZ5lz

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

@AccessAnalytic

Жыл бұрын

Thanks

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

@AccessAnalytic

2 жыл бұрын

It depends, the refresh will take longer but the visual interaction / page load could be quicker

@zzota

2 жыл бұрын

I like to get this sort of calculation done as close to the data as possible, so Power Query would be my first choice.

@050224011

2 жыл бұрын

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

@AccessAnalytic

2 жыл бұрын

Check this out dobbsondata.co.uk/2022/02/18/data-model-vs-dax/

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

@AccessAnalytic

Жыл бұрын

Good stuff

Thank you for the video!! How would you do this if you wanted to look at number of occupants for each month?

@AccessAnalytic

9 ай бұрын

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?

@AccessAnalytic

3 ай бұрын

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 kzread.info/dash/bejne/i4BrqpOLocbRkpM.html Time table; kzread.info/dash/bejne/X6Vr2JdvoJSfYrA.htmlsi=y4P7MZ4BQK1q6em_

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.

@AccessAnalytic

2 жыл бұрын

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

@hectorluisgaitan996

2 жыл бұрын

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

@AccessAnalytic

2 жыл бұрын

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

Hey in the DAX video you showed the monthly version after the daily solution, do you have one for power query?

@AccessAnalytic

Жыл бұрын

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?

Nice video. Could you please post the file too?

@AccessAnalytic

2 жыл бұрын

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

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.

@AccessAnalytic

Жыл бұрын

Possibly add some sort of IF statement to capture that scenario

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

@AccessAnalytic

Жыл бұрын

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

Question: shouldn’t you add 1 to the duration to include the day of check out?

@GeertDelmulle

2 жыл бұрын

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.

@AccessAnalytic

2 жыл бұрын

Interesting Geert, I think there’s use cases for both approaches and the specific scenario will influence the decision

@AccessAnalytic

2 жыл бұрын

I don’t think I’d add the checkout date as the person is not there that day

@AccessAnalytic

2 жыл бұрын

Also, it wil depend on what other data you include in the expanded table. A single column with low cardinality should compress well

@GeertDelmulle

2 жыл бұрын

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

I can't do that in my data. it does not show the right results

@AccessAnalytic

9 ай бұрын

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

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?

@AccessAnalytic

Жыл бұрын

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