DAX and the Start Date End Date Problem aka Events In Progress
ฝัง
- เผยแพร่เมื่อ 25 ก.พ. 2022
- Calculating the number of people present or processes running at the end of the data or during the month is tricky and requires some clever DAX thinking
I've also now done a Power Query version of this
• Events in Progress - d...
You can download my file and the source data here
aasolutions.sharepoint.com/:f...
Here are the formulae (NOTE that angled brackets aren't allowed in these TH-cam comments so I've used the words LESSOREQUAL and GREATER instead. You will need to replace those .
Number of Guests Present at end of Period =
VAR EndDatePerVisual = MAX('Calendar'[Date])
VAR RESULT =
CALCULATE(
[Number of Guests Checked In],
REMOVEFILTERS('Calendar'),
BookingsData[Check in Date] LESSOREQUAL EndDatePerVisual,
BookingsData[Checkout Date] GREATER EndDatePerVisual
||
ISBLANK( BookingsData[Checkout Date] )
)
RETURN
RESULT
Number of Guests Present during the Period =
VAR EndDatePerVisual = MAX('Calendar'[Date])
VAR StartDatePerVisual = Min('Calendar'[Date])
VAR RESULT =
CALCULATE(
[Number of Guests Checked In],
REMOVEFILTERS('Calendar'),
BookingsData[Check in Date] LESSOREQUAL EndDatePerVisual,
BookingsData[Checkout Date] GREATER StartDatePerVisual
||
ISBLANK( BookingsData[Checkout Date] )
)
RETURN
RESULT
Connect with me on LinkedIn and Twitter via
wyn.bio.link/ - แนวปฏิบัติและการใช้ชีวิต
Hi Wyn. Great one! Lots of good ways to use the technique you demonstrated. Looking forward to studying it further. Thanks for demostrating and also for providing the sample file to follow along.. much appreciated! Thumbs up!!
Cheers Wayne. DAX does take a lot of thinking about
@@AccessAnalytic Hi Wyn, I have an issue that feels similar. Say I have a Table of purchase orders, each purchase order has a start and end date and a Value£.
It is really easy in my head to envision a £3k order with a 3-month duration, apportioned £1k per column for three months.
But doing this in DAX, aggregating all PO's, where start and end dates are mid month is hurting my head. Is this something you have covered?
@@justmejustme4444 that's a tricky one. I think I would add a Power Query custom column with a calculation of "Daily Portion Amount" and then use that column with the technique in the video
or if you don't have tens of thousands of orders you can also use this Power Query method: th-cam.com/video/ISDhR-TzwJk/w-d-xo.html
I can't believe how many hours I spent on this problem before finding your video... But I'm seriously grateful I did! Thank you so much for the clear and concise explanation and demonstration. I was able to apply this to a budget involving a Type 2 SCD "Fixed Expenses" table. The table contained Effective and Expiry Dates associated with various "fixed" expenses, and I was able to achieve a sum of only those in effect during the target budget month, which was then incorporated into a calculation of discretionary funds remaining. Thanks again!
Excellent, glad I could help. I appreciate you taking the time to let me know you found it useful
I was struggling with this problem for a long time and could not find a solution even with Chat GPT. This video solve it in a simple and concrete way. Thank you
Glad to help and I appreciate you taking the time to let me know you found it useful
I really can't thank you enough. I've been trying to work on it for the last two days with no luck.
I appreciate you taking the time to let me know you found it useful
Fantastic explanation Wyn! Seriously helpful. Thank you for taking the time to get this into the public domain!!!
You're welcome, thanks for the kind feedback
Thanks Wyn. I enjoy that you use useful scenarios and that the problems are simple to understand and the model doesn’t detract from the example and solution. The way you simplify the language and thee explanations are the real magic sauce. Thanks for these gems.
I appreciate you taking the time to let me know you found it useful and you enjoy my videos
Wyn i really must say THANK YOU, i've been struggling for 4 days, wrapping my head around this specific case!
This is by far the most clear, straightforward and helpful explanation to this issue.
(Btw, you gain a stable subscriber)
Thank you - I’m glad the video was useful
Having started with PBI a couple days ago, this problem broke my head! I'm stuck thinking in VBA, but this video finally cleared the main differences up. Thank you!
Glad it helped. Yes DAX is a very different way of thinking. It’s all about how to apply and modify filters
@@AccessAnalytic :D
Wow, you are amazing, I was trying to do something similar in a report using all, filter and what not and never worked. Your solution works perfectly! Thank you!
You’re welcome, thanks for the kind comment
Supreme. Excellent tutorial. Thank you very much Wyn!
Thank you as always Iván
Wow, it seems so easy when you explain it.... but so difficult when you have to do it by yourself. Thanks a lot
DAX is a tough concept. Learning what to write is difficult.
Hi Wyn, thank you for this amazing explanation (especially the part where we had to breakout from any existing filters). Easy to follow and understandable! After hours of scouting through online-forums and even asking the almighty ChatGPT, yours was the only solution that worked. I needed to use this logic for a Human Resource Analysis, counting employees that were with the company at any specific point in time. Will be using this fantastic solution for years to come!
Very glad to help. I appreciate you taking the time to let me know you found it useful
Hi Wyn, thank you so much for an amazing video. Really well explained and broken down. I was stuck on this same problem for months and couldn`t find a single video online that solved the issue until I came across yours. I`m so happy, finally I have my dashboard working as intended...! Thank You...! 😀
Excellent. I appreciate you taking the time to let me know you found it useful
I am new to DAX - your presentation showed me the value of DAX
That’s great Gary
Exactly what I was looking for... Thank you!
Glad to help !
YOu are a life saver, Sir. Thank you very much for your time and effort.
You’re very welcome. I appreciate you taking the time to let me know you found it useful
Great solution, will definitely be using it! Thanks a lot Wyn!
No worries Ka, thank you, I’m glad you liked it
Thanks Wyn, as someone in the hotel business I will certainly be using this.
Perfect Ron!
Subbed and liked. Great video. I've been trying to resolve this exact scenario for over half a year now, just getting stuck and doing workarounds instead. Thank you for the great explanation!
You’re welcome. I appreciate you taking the time to let me know you found it useful
Thanks a lot, very useful video. Need more videos like this.
Thanks Fahad, I appreciate that.
Beautifully explained.
Thank you Erica
Currently doing lease contract start and end date. This gives a head start. Thanks
Excellent
Very useful and insightful !. Thank you Wyns.
You’re welcome Lorenzo
You’re welcome Lorenzo
This a great, well explained video and a great method. Well done
Thank you Ashley
Nice calculation! :-)
A couple of years ago (before COVID) I did a similar calculation like this: calculate the number of people present in a building based on batching information. The resolution was 5mins. Took the max per day. Then I made it into a histogram (I calculated myself in DAX) where I calculated the number of days a capacity was reached per period (months, years,…). Based on that information my organisation can effectively manage the space allotted to the various departments. It was a truly Big Data exercise - in order to boost the performance I ported it from Excel to Power BI.
Good stuff Geert
@@AccessAnalytic Thanks. :-)
Excellent! Lots of help!
Great to hear!
Very useful, great explanation. Many thanks
You’re welcome Roxana
Thanks for taking the time to leave a kind comment
This is brilliant. TH-cam algorithm brought me here. I need this for hire start date and hire end date, but the same logic applys.
I can’t imagine I’d have reached this conclusion by myself, so thanks very much.
Very concise and clear with a great working example.
You’re welcome, stay tuned for the video I release next Sunday, it solves the same issue but using Power Query rather than DAX.
@@AccessAnalytic question - for your solution do both the start and end date have to be linked to the calendar date in order for this to work, or just the start date? Thanks
To be honest it doesn’t matter, it doesn’t need to be connected at all for the calc to work. It’s more related to the slicer when you want to filter the chart down for a particular period
@@AccessAnalytic thanks, look forward to the PQ solution
Very useful, I'll definitely be using that.
That’s great, thanks for letting me know
100% Useful... Thank a lot for sharing¡¡¡
Thank you Arnau, it’s fantastic when people leave kind comments.
Thank you very much! This helped me a lot!
Great! Thanks for taking the time to let me know it was useful
The thanks go to you!
😀
great explanation
Cheers
Very useful thanks! Fantastic to have a video on this problem... Possible further video idea on this topic ... getting the demographics of guests in period...I've been doing dax formula with add columns to my "guest" table and then filter if present during period, return no. males etc...I'm hoping there's an easier way very repetitive to do for all the different fields.
Update: found the power query version perfect!
Glad to help 😁
Thank you for the simple explanation. Some time back I spent a LONG time trying to build a report that shows the total number of End Users my companies IT department supported by Month. I could not figure it out. I was able to apply your explanation to my use case. The only difference being, I did a CountRow() instead of referencing a measure.
One question I had on this, is why did you need the "End of Period by Date" measure. From my test with it, the second measure you wrote, returns the same results. While I can think of a couple of use cases for things like Inventory management, I couldn't figure out why you'd need it in your example.
Either way, thanks again.
Yeah you’re right, the 2nd approach works for most scenarios.
Thank you SO SO much!
You’re welcome Janette
@@AccessAnalyticUsing this to calculate Sq ft of storage needed based on equipment being delivered and being pulled out of storage for use. You're explanation of everything in this video is so helpful! Thanks again!
Fantastic Janette
This is a great explanation, Wyn, thank you!
Question: would it work for Datetime columns (aka to find the busiest hours, as I am calculating jobs in progress)?
Cheers. Probably a way. Normally you’d split date and time into separate columns and have a time table along with a date table. Same sort of concept though.
Hi Wyn, that is really great!!! One question, home can I get a total sum at the end, not only a last month data? I would really appreciate!!! KR
Can you give a bit more context and example please. It’s been a few years since I recorded this video
Hi Wyn, Thanks for such a great and informative video... I was working on similar problem... I have the resource allocation from start date to end date.... I need to consider the average meaning divide by 12, However when we have start date or end date in between the year we need to sum up for the months available and divide By 12
example
resource allocation start date end date
0.3 Aug 2021 March 2023
the result should show the allocation for 2021 0.3*5/12=0.125
the result should show the allocation for 2022 0.3*12/12=0.3
the result should show the allocation for 2023 0.3*3/12=0.075
That's a tricky one that would be best posted to somewhere like here: community.powerbi.com/t5/Desktop/bd-p/power-bi-designer
or
www.reddit.com/r/PowerBI/new/
Wow, such a good explanation and very easy to follow. Saved me a lot of time and headache. You are a talented teacher. Thank you and keep up the good work!
Thank you for that kind comment
Hi Wyn, I've been working on an Events in Progress report for the past week and wish I'd found your video sooner. Best explanation of all the videos and articles I've seen so far as yours is the only example I've come across that specifically addresses the issue of no EndDate for events that are still in progress.
Two quick questions as I had to do my report in Power Pivot:
Since REMOVEFILTERS isn't available in Power Pivot, I used ALL on my Date table. The results appear correct and consistent but I'd like to make sure that's the right approach.
Second, in my Data Model I've used two inactive relationships to connect my Date table to the StartDate and EndDate in my fact table and I've used USERELATIONSHIP when specifying measures that only look at one of those dates; is this the correct approach?
Thanks again. This is a huge time saver for me!
Thanks George. Your methods listed above are correct. Good one!
Hi Wyn, how would I link the graphic of this measure to a table which provides me with the info of who was in the "hotel" during the the month that's displayed in the visual? So that I can just check the figures and see which names were present during that month.
I've tried creating a relationship between the Date table and my data table, but it just breaks the graphic and displays the number of people who checked in/checked out during that month instead of who was present during the month (depending on which column I make the relationship with, whether it's Check in date or Check out date). Thanks!
You’d probably need a different approach - like this Events in Progress - displaying items present between two dates in Power BI / Excel with Power Query
th-cam.com/video/ISDhR-TzwJk/w-d-xo.html
Great video. Thanks for these measures.
Is there a way to show the list of guests for the selected date?
Thanks, for that I think you’d need a different approach : th-cam.com/video/ISDhR-TzwJk/w-d-xo.html
@@AccessAnalytic Thanks a lot. This is amazing.
No worries
Brilliant.Similar to my problem but it on involves times/hours. how do we do this if we have 'times' involve? For eample how many paramedic are on incidents at a given hour of that week ?
Same approach but using a Time Table rather than a Calendar Timetable. Here's a video about creating a Time table th-cam.com/video/-q7v56p192M/w-d-xo.htmlsi=-ZLJr65YkjBwLKaW
👏👏👏👏
☺️
Great explanation thank you for the effort, I have one question please:
If I want to drill through to a detail page with a table that shows the names and another personal information.
How can I achieve that? as the table will not have all the Dax filter remove and change in the date as measure.
I really appreciate if you can help me in this
I wonder whether this approach would work better for you th-cam.com/video/ISDhR-TzwJk/w-d-xo.html
Hi Wyn, awesome video!
I've got a question though. If I'm trying to forecast the number of employees for the next few years (I have their planned leaving date) what edits should I do to the DAX?
With the CurrentDate as in the video, it is calculating only till today(as I have my last day in my calendar table set to today).
But when I changed last day to dec 31 2040, the DAX is not working as expected.
Thanks! Hope you answer it.
It should in theory work so I’m not sure what the issue is sorry. Are you displaying the values in a table / chart by month?
@@AccessAnalytic Thank for responding! Nope, I'm displaying with week numbers.
May I ask what the last date in your date table is?
When my last date is Today() and in the DAX, EndDateperVisual(MAX(date)), the calculation in working just fine. However, I need to forecast for the future with some definite ending dates. So my date table needs to be having dates till 2040. But extending the date table is screwing the calculations.
My end date is the end of the calendar year. I can’t think of a reason why the Calendar end date should make any difference. I’m thinking there’s something else going on. Do you have Year and week number in your chart?
Hi Wyn
U follow your scenario based problem solving, using DAX.
I love DAX, however my ability to grasp is limited.
This video is very useful, thank you.
I have a scenario, which is tricky (may be for me😊)
My report having Est Ship date, and total units etc.,
I have a calendar table and created relationship between main report and calendar table.( Month, month number, date , date number, year, year number)
My production start date 8th of one month, and production end date is 7th of following month. Example: march production month means, 8th march to apr 7th.
I want to select only month/year in a slicer.
It should give me total units in the selected month, within the production month i stated above.
You need to add a new month column to your Calendar table for production month that covers the date you require. That can be done with conditional formatting logic or simply build your calendar table manually in Excel.
Great video! Just to remind, REMOVEFILTERS is not available in some old versions of Power BI Desktop either... :(
Cheers, I think it was released towards the end of 2019, so anyone using that desktop version is in a whole world of trouble!
@@AccessAnalytic unfortunately i am
@@wmfexcel - oh wow - how come?
@@AccessAnalytic coz my company is using a very old version of Report Server... where I publish my report to... that's the real business world. Isn't it? I know many companies are using Excel 2010... or even Windows XP, believe it or not. haha
Ah such a shame and often see this false sense of “money saving” from companies
Thanks so much for this video! Really helped me out for a very large manning roster. However i have an issue where I'm not able to drill down to show details in my power pivot table and I'm getting some strange numbers
Would you know why i can't drill down to see on my power query pivot table the filter context results? I do have quite a lot of date columns in my data table I'm trying to do it on so i want to see where I'm going wrong :)
The filter modifiers within a measure cannot be used to filter your physical rows in your pivot or drill down.
If you need the details of the people who are present each day then you might need to pre build that with a pivot listing all the people.
Alternatively use a Power Query approach to this issue like in this video th-cam.com/video/ISDhR-TzwJk/w-d-xo.htmlsi=3vBxp2SX7AcYsGCs
@AccessAnalytic I did see that power query video and thought of giving it a go, but my issue is this project is going to run across years and so expanding out hundreds of names even on a weekly basis might be a bit slow.
I have tried pre populating the pivot table with the people's info as fields, but that's when I start to get strange results (for example, the counts work in some months, but doesn't in others). I know the issue lies in my logic since I have an extra layer of end dates, since I'm essentially doing a head count of an office then tracking when people finish on the project or move to site, which I handled via a conditional column on power query but yeah I'm getting a bit stumped at my counts.
Do you have a rule of thumb on which method (power query vs measure) works better? I'm now inclined to try the power query method as you suggested if I'm able to check my logic via drilling down that way
@TheFishLamp I think if you need the detail then Power Query might be the better approach but it can get a bit messy trying to avoid duplicating other rows you need ( separate tables required etc )
`Very nice and very usefull. I would also like to have the names of the people during the period. How to accomplish that?
You might need to use the Power Query approach
th-cam.com/video/ISDhR-TzwJk/w-d-xo.html
Although maybe you could do a CONCATENATEX function iterate through all the names joining them together with CHAR(10) as the separator to force a new line for each name.
Thank you for this. It works really well, but when I create a drill through from it, it doesn't return the correct records and returns records that have closed prior to the end date. Anyone know where I am going wrong? Thanks
Ah yes a measure can not pass filter context through to a drill through page. You may need to go down the Power Query approach if you need a breakdown th-cam.com/video/ISDhR-TzwJk/w-d-xo.htmlsi=1g5WQX-aBVr6xbyt
Could you please do a separate video on currency conversions through dax measures ?
Not something I have any experience of sorry. Have you seen these?
www.sqlbi.com/articles/currency-conversion-in-power-bi-reports/
www.sqlbi.com/tv/solving-currency-conversion-unplugged-n/
@@AccessAnalytic I did. Too hard to interpret though🙈
how to do it by Hour or by Minute?
and also how to measure if 2 separate entities are both present?!
like for example an Aircraft and Mechanic by Hour by Station
I’ve never tried that but similar principals I’d say, maybe using a Time Table along with a Calendar table.
I’d post that type of question here for a better response than I can provide
community.powerbi.com/t5/Desktop/bd-p/power-bi-designer
Hello Wyn, I tried following the same method for my dataset and found similar results except I believe I'm doing something wrong. I have a start date of 4th april and end date of 4th June, total period of 60 days. When I filter the date range instead of showing 26,30,4 distribution it shows me 60,60,60 distribution. Is there a way to calculate the avg of each day for given month range or I need to create a new measure for this?
Sounds like a missing relationship between the table where your date axis is coming from and the table containing the data
Calculating the average each day could just be a DIVIDE by number of days where occupancy 0 or may be more complex requiring use of AVERAGEX over a temporarily created date table. That needs a bit of thought and worth posting some example data and the question to the Power BI Community Forum
Hi Wyn
Very helpful!
I have a question. How could I go about creating an inflow count and outflow count? I would like to split the bars into Inflow (started this period), Current (started before this period) and Outflow (stopped this period).
I am quite new to DAX, but I feel like this isn't very difficult when you have done the steps in this video. What would your advice be?
EDIT: I figured it out! It took some doing, but I landed on these measure:
Inflow = CALCULATE(
[Number of guests],
'Guests'[Start date] >= MIN('Date'[Date]) &&
'Guests'[Start date] = MIN('Date'[Date]) &&
'Guests'[End date]
Glad you found a solution
I got an error saying can't apply < filter on date field. Any idea?
Not sure sorry. Are you using an up to date version of Power BI?
I know this was a few months ago, eight to be exact, but what if the customer checked out on 1-Feb? I'm referring to your second formula. It says check out date greater than start date per visual. If the visual context is month then for February, min(calendar[date]) = 1-Feb. Would your formula count a guest who checks out on 1 Feb?
Thank you
Never mind. I realized that if the customer leaves on 1-Feb that customer should not end up in the official count for for the month of February. That is why it the formula was ">" than rather than ">=" . Thank you
Hi, I can't quite remember if I did it this way but my thinking now is that someone who checks out on a day should not be counted on that day.
You’re welcome
Hi Wyn. I'm trying to solve a similar issue. Namely I have Start, End dates and Value for each row. The period between Start and End is called "Open". I'm trying to create a DAX formula that will summarize the Value of all rows that are "Open" in a time period (I need to use months to be precise - and to show the result in matrix/bar chart). I have tried your solution and other 10 or so, and none of them works.
Example:
When I filter the data using Start date before or on 28th Feb 2022, and End date on or after 1st March 2022, I get a different sum of values than for the matrix or bar chart visualizing February 2022.
What could I be doing wrong?
Wouldn't open during February be a filter on all those with a start date on or before 29th Feb and an End date on or after 1st Feb ?
@@AccessAnalytic you are right. I have solved it using Events in progress by DAX Patterns.
Don’t you ought to remove the start date min variable there for anyone that checked in before January (say Dec 31 for NYE) and checked out in or after January?
Edit: Great vid btw, added to my work playlists :) came here looking for solution to this problem for ticketing, new to DAX so wish me luck😊😊
Hmm, I don't think so as the formula includes anyone checked in prior to EndData who checks out after StartDate
@@AccessAnalytic ok thanks for responding and clarifying, i think that bit went over my head 😅
It’s a difficult topic, there’s still a huge amount I don’t know
how do you get the calendar excel?
Hi, you can download one here accessanalytic.com.au/download-your-own-power-query-calendar/
@@AccessAnalytic Thank u!!
I have an additional dimension to add to this that I'm hoping someone can help me with - What if we wanted to perform this kind of calculation monthly per hotel (where multiple hotels). So each column would be a month in the min max range, the rows would be each hotel, and the values in each cell would be days count per hotel per month. Is that even possible? This problem is breaking my brain :/
On the face of it it sounds like just dropping Hotel name into the rows of a matrix visual and Month into the columns
Hi there! I see that in this table you had the number of guests between two dates. How would you write this DAX code if you had a table with customer ID's and their start/end dates? This video is exactly what I'm looking for, however i'm not sure how i would edit the function to suit what I need, any helo would be massively appreciated!
Hi, the formula is in the description section. You should be able to swap out your table and column names for the ones I’ve used. It assumes you also have a Calendar table.
@@AccessAnalytic Hello, thank you for your reply! I'm talking about the first DAX measure in the video which was Number of Guests Checked in = SUM(BookingData[Number of Guests Column]). Your measure is SUMming the numbers in your Number of Guests' Column. However instead of that, I have a 'Guest Name' column, how would I replicate what you've done with Names instead of the number of guests? Would I just use a COUNTA function to count all the rows - would that work? Thank you in advance.
@@lukehughes4970 Ahh, you can use =COUNTROWS( YourTable )
@@AccessAnalytic i got it to work by using the COUNTA function. Thanks again!!😊
As long as there’s no blanks that works fine
Um, how do you calculate Step 1: Number of Guests Checked In.....the lesson starts on Step 2: How to Calculate Events in Progress.
It assumes your data has check in date check out date and number of guests. What data do you have?