The ultimate Microsoft Fabric, Power BI, Azure AI, and SQL learning event: Join us in Stockholm, September 24-27, 2024.
Save €200 with code MSCUST on top of early bird pricing!
Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started
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
Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.
Check out the August 2024 Power BI update to learn about new features.
User | Count |
---|---|
111 | |
82 | |
63 | |
54 | |
52 |
User | Count |
---|---|
127 | |
118 | |
81 | |
65 | |
64 |