DAX measure with start AND end date for Power BI

Power BI can only handle one active relationship between two tables at one time, but you can work around this limitation with DAX, allowing you to get a measure dependent on a start & end date using DAX (and not using Power BI's relationships).

Some common examples include number of staff, students, customers or quantity on stock that can be filtered for any date. In each case people/stock has a start & end date (or maybe no end date if they are still there), and its common to want to look at this based on date filters, e.g. how many staff did I have on 3 May etc. You need a few combinations to make the DAX work, and I go through that in my new video.

00:00 - Introduction
03:25 - Headcount
04:17 - Activate relationship
05:23 - Started on or before date
08:16 - End on or after
09:52 - What if people didn't t leave
10:36 - Left after or didn't leave
12:28 - Current
14:32 - Headcount currently or not left

[fredbarnes]

This actually works! I have looked at many solutions for the "Open Events over Time" type pattern on YouTube and this is the first one i could use.

[simonsax]

Great video and solution! This approach seems to be way faster than other solutions everyone else is showing here on youtube (at least for my chart).

[angied]

Ah my gosh this video saved me so much time - I have been pulling my hair out trying to work it out.

One question, I work in recruitment and I used this formula to calculate open roles so I can work out number of roles per recruiter per quarter. Now I need to work out average roles per recruiter per quarter and I’m scratching my head again. Any advice?

[franklinrivasmairena]

Great work! I’m curious, which measure are you using for the line chart titled 'Workers Currently by Date'?

[tinaflemons]

Great video, Can I use the same formulas if I changed out the date (day) filter to a month-year filter?

[merrillmchenry]

Nicee video and topic. Thanks. Where's the download pls?

[barttitulaerexcelbart]

Wow that is a great video, I will definitively save this for later use. This example can be elaborated on machine capacity or hotel occupation. This is a so common problem, , there should be a standard DAX formula for you know if there is a Quick Measure for this?