Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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!
Solved! Go to Solution.
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
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
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
111 | |
100 | |
80 | |
64 | |
58 |
User | Count |
---|---|
148 | |
111 | |
93 | |
84 | |
66 |