DAX measure with start AND end date for Power BI

Подписаться 15 тыс.

Просмотров 9 тыс.

50% 1

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

Хобби

Опубликовано:

14 июл 2024

Ссылка:

Скачать:

Готовим ссылку...

Добавить в:

Мой плейлист

Посмотреть позже

Комментарии : 12

@fredbarnes196 3 месяца назад

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

@learnspreadsheets 3 месяца назад

Thanks! Yesss I was in the boat for a while where I couldn’t find anything that fully worked so when I finally figured it out I made this video, partially to remind myself how to do it when I need to, & of course to help others! It’s such a super common pattern that it shouldn’t be so hard!

@simonsax9102 8 месяцев назад

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

@learnspreadsheets 8 месяцев назад

Thanks so much! Glad you found it useful. I also spent a while on those videos and tutorials as I learned this process so I thought I'd compile the best solutions and redo one as simple as I could!

@angied84 4 месяца назад

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?

@learnspreadsheets 4 месяца назад

I’m glad it helped, sorry but I can’t think of it, I’d need more context for that

@tinaflemons263 Месяц назад

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

@learnspreadsheets Месяц назад

I think you should be able to as long as you have a proper date table

@barttitulaerexcelbart9400 Год назад

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 this.....do you know if there is a Quick Measure for this?

@learnspreadsheets Год назад

Yes! I couldn’t agree more. It should be easier than it is but at the moment there is no easier way to do it