cancel
Showing results for
Did you mean:

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

## 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 Date Resolved Date/Time 01/01/2017 02/01/2017 02/01/2017 05/01/2017 03/01/2017 04/01/2017 05/01/2017 06/01/2017 07/01/2017 07/01/2017 09/01/2017 08/01/2017 20/01/2017 09/01/2017 20/01/2017 10/01/2017 20/01/2017 11/01/2017

The output should be like this \/

 Year Month Week Number of Open Incidents 2017 1 1 3 2017 1 2 4 2017 1 3 0 2017 1 4 0 2017 1 5 0

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!

1 ACCEPTED SOLUTION
Community Support

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

Community Support Team _ Qiuyun Yu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Community Support

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

Community Support Team _ Qiuyun Yu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Announcements

#### Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

#### Power BI Monthly Update - August 2024

Check out the August 2024 Power BI update to learn about new features.

#### Fabric Community Update - August 2024

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

Top Solution Authors
Top Kudoed Authors