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

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