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.
For a service desk application, I want to count the number of open cases a any given time. I have tow date columns "CreatedDate" and "SolutionDate". I case is considered to be open at any "given date" if:
CreatedDate < "given date" AND
SolutionDate > "given date" OR SolutionDate is BLANK
I've tried the COUNTROWS in DAX with filters, but cant make i work. Any help is appreciated.
My objective is to ceate a chart, which week by week show the development in the number of cases.
Thanks.
Solved! Go to Solution.
Oh, sorry, my bad
Open Cases =
CALCULATE(COUNTROWS(Table),
FILTER(ALL(Table),
Table[CreatedDate] < SELECTEDVALUE('Calendar Table'[Date]) &&
(Table[SolutionDate] > SELECTEDVALUE('Calendar Table'[Date]) || ISBLANK(Table[SolutionDate]))
)
)
Hi @torbenani ,
Please check following steps as below.
1# Create calculated table.
CALENDAR = CALENDAR(MIN('Table'[Created_on]),TODAY())
2# Create measures.
isopen =
IF (
SELECTEDVALUE ( 'Table'[Created_on] ) <= SELECTEDVALUE ( 'CALENDAR'[Date] )
&& (
SELECTEDVALUE ( 'Table'[Closed_on] ) >= SELECTEDVALUE ( 'CALENDAR'[Date] )
|| ISBLANK ( SELECTEDVALUE ( 'Table'[Closed_on] ) )
),
1,
0
)
counts = SUMX('Table',[isopen])
3# Use 'CALENDAR'[Date] as slicer.
Result would be shown as below.
Best Regards,
Jay
Community Support Team _ Jay Wang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
@torbenani , refer this blog can help you
Hi @torbenani
the best practice would be to create a calendar table
Calendar Table = CALENDAR(MIN(Table[CreatedDate]), MAX(Table[SolutionDate]) )
the add to it a measure
Open Cases =
CALCULATE(COUNTROWS(Table), FILTER(ALL(Table),
Table[CreatedDate] < SELECTEDVALUE('Calendar Table'[Date]) && (Table[SolutionDate] > SELECTEDVALUE('Calendar Table'[Date]) OR ISBLANK(Table[SolutionDate]))
Hi AZ38
Thanks a lot for your help. When I use the measure, I get " The syntax for 'OR' is incorrect. "Could you gudie me.
Oh, sorry, my bad
Open Cases =
CALCULATE(COUNTROWS(Table),
FILTER(ALL(Table),
Table[CreatedDate] < SELECTEDVALUE('Calendar Table'[Date]) &&
(Table[SolutionDate] > SELECTEDVALUE('Calendar Table'[Date]) || ISBLANK(Table[SolutionDate]))
)
)
az38 - Thanks for your help. This works perfect.
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 |
---|---|
113 | |
100 | |
78 | |
76 | |
52 |
User | Count |
---|---|
144 | |
109 | |
108 | |
88 | |
61 |