topic Re: Repeat case totals for each month from Open Date to End Date in DAX Commands and Tips

Repeat case totals for each month from Open Date to End Date

Power_It_Up — Fri, 12 Apr 2024 14:21:55 GMT

Hi.

The task here is to create a graph which shows the period of time a case was opened until closure (if not Closed, then default current date).

For example, Case # 10 was opened in February and is still open, so I have made an End Date calculation to show that if there is no Closed Date, then to default to current date.

The graph needs to represent this data as follows:-

So a value of 1 for each month the case has been opened until closure (End Date).

Have attached a test workbook.

Edit: Actually, not sure how to attach workbook.

Many thanks in advance for any pointers and advice. 🙂

Re: Repeat case totals for each month from Open Date to End Date

quantumudit — Sat, 13 Apr 2024 10:37:37 GMT

Hello Power_It_Up

Please provide a sample anonymized dataset. Need to understand the data model to form an appropriate solution

Best Regards,
Udit

🚀 Let's Connect: LinkedIn || YouTube || Medium || GitHub
✨ Visit My Linktree: LinkTree

Re: Repeat case totals for each month from Open Date to End Date

DataNinja777 — Sat, 13 Apr 2024 10:49:50 GMT

Hi Power_It_Up

You can write a dax formula like below to get the output you require.

I attach a pbix file as an example.

Re: Repeat case totals for each month from Open Date to End Date

Power_It_Up — Mon, 15 Apr 2024 13:38:16 GMT

Hi DataNinja777 and many thanks for your reply and test sample.

Nearly there. Two examples....

1) Case #7 was opened in March and closed in April. I would expect to see a bar for both March AND April with a count of 1 in each:

2) Case #10 was opened in March and is still open, so the below is spot-on:-

So it seems that with closed cases, it only plots for the month the case was open.

Many thanks again! 🙂

Re: Repeat case totals for each month from Open Date to End Date

Power_It_Up — Mon, 15 Apr 2024 13:39:24 GMT

quantumudit Many thanks for your reply. I can't seem to attach a workbook, so couldn't provide any sample data and only by screenshot. 🙂