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

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
RefineryUnit
Regular Visitor

Calculate number of open cases for each day of the year

Hello,

 

Table1 contains data of the following nature:

Case #Open DateClose DateOwner
1a6/1/20216/5/2021Alice
1b6/4/20216/22/2021Bob
1c6/3/20216/17/2021Bob
1d6/16/20217/1/2021Alice
1e6/12/20217/15/2021Bob

 

What I'm trying to do is to calculate the number of open cases for each day of the year for each owner. So I have a Calendar table that lists every day between 1/1/2021 and 12/31/2021 but I haven't figured out quite yet what kind of formula I should use for this. For example, the case 1a was open on 6/1, 6/2, 6/3 and 6/4. On 6/5 it was closed so we don't include that. Case 1b was opened on 6/4 and closed on 6/17, which means on 6/4 we had a total of 2 cases open (1a and 1b) or 1 per person. On 6/5 it goes back down to 1 because case 1a was closed and only 1b was open.

 

Thanks in advance!

 

1 ACCEPTED SOLUTION
v-zhangti
Community Support
Community Support

Hi, @RefineryUnit 

 

Please check the following methods.

Measure:

Count = 
CALCULATE (
    COUNT ( 'Table'[Case] ),
    FILTER (
        ALL ( 'Table' ),
        [Owner] = SELECTEDVALUE ( 'Table'[Owner] )
            && [Open Date] <= SELECTEDVALUE ( 'Date'[Date] )
            && [Close Date] >= SELECTEDVALUE ( 'Date'[Date] )
    )
)

vzhangti_1-1655692229605.pngvzhangti_2-1655692250040.png

Is this the result you expect?

 

Best Regards,

Community Support Team _Charlotte

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

View solution in original post

1 REPLY 1
v-zhangti
Community Support
Community Support

Hi, @RefineryUnit 

 

Please check the following methods.

Measure:

Count = 
CALCULATE (
    COUNT ( 'Table'[Case] ),
    FILTER (
        ALL ( 'Table' ),
        [Owner] = SELECTEDVALUE ( 'Table'[Owner] )
            && [Open Date] <= SELECTEDVALUE ( 'Date'[Date] )
            && [Close Date] >= SELECTEDVALUE ( 'Date'[Date] )
    )
)

vzhangti_1-1655692229605.pngvzhangti_2-1655692250040.png

Is this the result you expect?

 

Best Regards,

Community Support Team _Charlotte

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.