Forum Discussion

dcs136's avatar
dcs136
Advocate I
9 years ago
Solved

Count Open Incidents Weekly

Hi Guys!  

 

I'm trying to create some DAX to solve this problem, because I can't do this using my database.

 

Here is the thing!  

I want to create a chart that shows me all the Open Incidents Weekly.     

 

My source look like this

 

Submit DateResolved Date/Time
01/01/201702/01/2017
02/01/201705/01/2017
03/01/2017 
04/01/2017 
05/01/2017 
06/01/201707/01/2017
07/01/201709/01/2017
08/01/201720/01/2017
09/01/201720/01/2017
10/01/201720/01/2017
11/01/2017 

 

The output should be like this \/

YearMonthWeekNumber of Open Incidents
2017113
2017124
2017130
2017140
2017150

 

As you can see I'm using the last week day to count the Open Incident.

 

To do that in the database i used this formula below \/

 

((datediff(day, [Submit Date], [CalendarDate]) >= 0) AND ((datediff(day, [Resolved-Date/Time], [CalendarDate]) <= 0) OR ([Resolved-Date/Time] is null)))

 

The chart should be like this \/

 

 

 

Thank you very much!

 

 

Need Help
  • v-qiuyu-msft's avatar
    v-qiuyu-msft
    9 years ago

    Hi dcs136,

     

    From your description, the logic is to count the incident which is opened in a specific week but isn't closed in this week, right?

     

    If that is a case, you can create a measure like below:

     

    Measure 2 = 
VAR m =
    CALCULATE ( MIN ( 'Date'[Date] ), ALLEXCEPT ( 'Date', 'Date'[WeekNum] ) )
VAR MX =
    CALCULATE ( MAX ( 'Date'[Date] ), ALLEXCEPT ( 'Date', 'Date'[WeekNum] ) )
RETURN
    CALCULATE (
        COUNTROWS ( Table3 ),
        FILTER (
            'Table3',
            AND (
                'Table3'[Submit Date] >= m
                    && Table3[Submit Date] <= MX,
                'Table3'[Resolved Date/Time] > MX
                    || Table3[Resolved Date/Time] = BLANK ()
            )
        )
    )

     

     

    Best Regards,
    Qiuyun Yu

1 Reply

  • v-qiuyu-msft's avatar
    v-qiuyu-msft
    Community Support
    9 years ago

    Hi dcs136,

     

    From your description, the logic is to count the incident which is opened in a specific week but isn't closed in this week, right?

     

    If that is a case, you can create a measure like below:

     

    Measure 2 = 
VAR m =
    CALCULATE ( MIN ( 'Date'[Date] ), ALLEXCEPT ( 'Date', 'Date'[WeekNum] ) )
VAR MX =
    CALCULATE ( MAX ( 'Date'[Date] ), ALLEXCEPT ( 'Date', 'Date'[WeekNum] ) )
RETURN
    CALCULATE (
        COUNTROWS ( Table3 ),
        FILTER (
            'Table3',
            AND (
                'Table3'[Submit Date] >= m
                    && Table3[Submit Date] <= MX,
                'Table3'[Resolved Date/Time] > MX
                    || Table3[Resolved Date/Time] = BLANK ()
            )
        )
    )

     

     

    Best Regards,
    Qiuyun Yu