Measure to calculate current closed risks

[mariella2]

Hi, I need help with a measure to calculate current closed risks. I have the risk table linked to the calendar[Date] table with an active relationship in Creation Date and one inactive for Mitigation Date (Closing risk date).
The measure I use is the following:

Mitigated risks = var selectedDate = Max('Calendar[Date]) VAr FilteredTable = Filter('Risk', 'Risk'[Mitigation Date] <= selectedDate && 'Risk'[Mitigated]="Mitigated") RETURN Countrows(FilteredTable)

This measure works ok, but when I use it in a visual and filter by a slicer with Calendar[Date] as the field, it behaves strangely.
If I select the first semester (H2 2021), it displays the correct count of closed risks,

if I select the next semester (H1 2022), it goes blank and it is blank for all subsequent semesters.

This shows that the measure works with the first date in the slicer:

This is to show that starting from the next semester in the slicer, the measure goes blank.

What is missing in the measure so that it displays the correct count for each semester?
Any help is appreciated.

Edit: if I add 'ALL' to the measure, it works ok for the semester. But if I add it to bar chart with risk owner on the other axis, I get the same total per each user.

Mitigated risks = var selectedDate = Max('Calendar[Date]) VAr FilteredTable = Filter(ALL('Risk'), 'Risk'[Mitigation Date] <= selectedDate && 'Risk'[Mitigated]="Mitigated") RETURN Countrows(FilteredTable)

Tables:

Risk Id	Creation semester	Creation Date	Mitigation Semester	Mitigation Date	Mitigated
1	2021 H2	31/12/2021	2022 H1	30/06/2022	Mitigated
2	2021 H2	31/12/2021	2021 H2	31/12/2021	Mitigated
3	2022 H1	30/06/2022	2022 H1	30/06/2022	Mitigated
4	2022 H1	30/06/2022	2022 H2	31/12/2022	Mitigated
5	2022 H2	31/12/2022			Open
6	2022 H2	31/12/2022	2023 H1	30/06/2023	Mitigated

[mariella2 · Accepted solution]

Solved with the following dax: 

= CALCULATE([Total Mitigated Risks], DATESBETWEEN('Calendar'[Date],BLANK(),MAX('Calendar'[Date])))

where Total Mitigated risks = 

CALCULATE(COUNT('Risks'[Mitigated]),'Risks'[Mitigated]="Mitigated", USERELATIONSHIP('Calendar'[Date],'Risks'[Mitigation Date]))+0

[mariella2]

Hi, I am bumping this post again as I still need your help.

I've modified a little bit the measure to this:

Mitigated current risks = VAR MaxDAte = MAX('Calendar'[Date]) REturn

CALCULATE(Countrows(Filter(ALL('Risks'), 'Risks'[Mitigation Date] <= MaxDate && 'Risks'[Mitigated]="Mitigated")), USERELATIONSHIP('Calendar'[Date],'Risks'[Mitigation Date]))

What happens is that I get the right current number of the mitigated risks for each of the semesters(meaning that the mitigated risks for the first semester are added to the mitigated risks of the next semester, and so on).

What I'd like to do with this measure is to show the current mitigated risks for each risk owner, but when I put them into table, it shows the total current mitigated risks for each risk owner. 
If I remove the 'ALL' in the measure, I get the mitigated the risks for that semester not in addition to the previous one, which is what I am looking for.
How can I solve it? Thank you in advance for your help.

[mariella2]

any help is much appreacited.

[mariella2]

any suggestions please?

[Ritaf1983]

Hi @mariella2 

Please provide sample data that covers your issue or question completely, in a usable format (not as a screenshot).
https://community.powerbi.com/t5/Community-Blog/How-to-provide-sample-data-in-the-Power-BI-Forum/ba-...
Please show the expected outcome based on the sample data you provided.

https://community.powerbi.com/t5/Desktop/How-to-Get-Your-Question-Answered-Quickly/m-p/1447523