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

[Power_It_Up]

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

[DataNinja777]

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.  

[Power_It_Up]

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! 🙂

[quantumudit]

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

[Power_It_Up]

@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. 🙂