Start and End Date Multiple Date Measures

[PowerBI-Noob]

I am really struggling to get powerBI to work for me. Having been trying with chatGPT but it is even more useless than me so going to ask some real experts!!

I have a data table that records tasks that are being done.

Each task has a start and end date. Where the task is still being worked out it will be null.

I created custom columns so that the end date always has a valid date even when null. So I use today+1. Then open is anything with endate<today.

I need a measure that will calculate the number open cases and how this has changed over time, i.e. graphing it.

But I am really struggling to get it to work. It seems like a relatively common use case but don't know if I should transform my data first to make it easier to use.

Any advice greatly appreciated 

[OwenAuger · Accepted solution]

Hi there @PowerBI-Noob 

Take a look at the Events in Progress pattern on DAX Patterns.

There are two options presented there:

1. Leave your Tasks table structured as-is, and write measures as shown in the "Open Orders" section.
2. Transform your Tasks table so that it contains one row for each date that a given Task is open, as show in the "Open orders with snapshot" section.

Please give that a try and post back if you need some more help 🙂

Regards