Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply
Power_It_Up
Helper I
Helper I

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

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:-

 

 

Power_It_Up_0-1712930992957.png

 

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

4 REPLIES 4
DataNinja777
Super User
Super User

Hi @Power_It_Up 

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

DataNinja777_0-1713005324770.png

DataNinja777_1-1713005352251.png

I attach a pbix file as an example.  

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:

Power_It_Up_0-1713188065879.png

 

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

Power_It_Up_1-1713188208671.png

 

 

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

 

Many thanks again! 🙂

 

 

 

quantumudit
Continued Contributor
Continued Contributor

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

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

Helpful resources

Announcements
Europe Fabric Conference

Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

Power BI Carousel June 2024

Power BI Monthly Update - June 2024

Check out the June 2024 Power BI update to learn about new features.

PBI_Carousel_NL_June

Fabric Community Update - June 2024

Get the latest Fabric updates from Build 2024, key Skills Challenge voucher deadlines, top blogs, forum posts, and product ideas.