Forum Discussion

cas2000's avatar
cas2000
Frequent Visitor
5 years ago
Solved

Calculate back in time

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!

 

 

Need Help

7 Replies

    • cas2000's avatar
      cas2000
      Frequent Visitor
      5 years ago

      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

  • Anonymous's avatar
    Anonymous
    Not applicable
    5 years ago

    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.