Calculate back in time

[cas2000]

Hello Community,

I'm a total Power Bi newbie, and I've been asked to create a report on Custmer returns cases. 

The database is tracking Receiving Date & Final Response date (closing date)

the report needs to track 3 things:

1) Number of cases created on the month. (done)

2) Number of cases closed on the month. (done)

3) number of open cases on the month. 

For #3, I need to know how many cases were open in that specific period.

Example: 

if a case X was created on January and closed on April, the report should count  this case as open on January, February & March.

Any idea on how to crate a measure to calculate this?

Thanks in advance for your help and support!

[v-rzhou-msft]

Hi @cas2000 

My Sample:

I build a date table by dax.

Date = ADDCOLUMNS(CALENDARAUTO(),"Year",YEAR([Date]),"Month",MONTH([Date]),"MonthName",FORMAT([Date],"MMMM"))

Please Try my measure.

Open Case on Month = 
VAR _T1 =
    GENERATE ( 'Sample', 'Date' )
VAR _T2 =
    ADDCOLUMNS (
        _T1,
        "a",
            IF (
                [Date]
                    >= EOMONTH ( [Created], -1 ) + 1
                    && [Date] <= EOMONTH ( [Closed], -1 ),
                1,
                0
            )
    )
VAR _T3 =
    SUMMARIZE ( _T2, [ID], [Year], [Month], [MonthName], [a] )
VAR _Sum =
    SUMX (
        FILTER ( _T3, [Year] = 'Date'[Year] && [MonthName] = 'Date'[MonthName] ),
        [a]
    )
RETURN
    _Sum

Result is as below.

Best Regards,

Rico Zhou

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

[cas2000]

Thank you very much!!

[Ashish_Mathur]

Hi,

Share some data to work with.

[cas2000]

Sorry for the delay, everytime I try to load the data in table format, I get an error.

Id    Created         Closed
1      01/02/2021   03/19/2021
2      01/09/2021   02/29/2021
3      02/05/2021   04/02/2021
4      02/19/2021   03/29/2021

#1 should be counted on Jan & Feb

#2 only Jan

#3 on Feb & Mar

#4 on Feb

[Ashish_Mathur · Accepted solution]

Hi,

You may download my PBI file from here.

Hope this helps.

[cas2000]

Thanks a lot for your help!!

[Ashish_Mathur]

You are welcome.