Stacked Bar Chart not showing correct values

[Sonnet]

Hi all, I have an issue. I have a stacked bar having on X axis Semester by Year from the Calendar Table, on the Y axis a measure that counts the current open risks based on the Risk Table. In the legend, Current Rating from the Risk Table.

The issue is that the rating changes whenever there is a change in the Current Rating in the Risk Table each semester instead of keeping the actual count each semester.

Here an example of the tables:

Risk Table

Risk Id	Creation Date	Current Rating for current semester
4	30/06/2023	Medium
1	30/06/2022	High
2	31/12/2022	Low
3	30/06/2023	Medium

Manually, every semester the current Rating for current semester is substitute with a new rating, and the old rating is stored in the Rating Table:

Risk Id	Semester Date	Rating
1	30/06/2022	Low
1	31/12/2022	Medium
2	31/12/2022	Low
2	30/06/2023	High
3	30/06/2023	Medium
3	31/12/2023	Medium
4	30/06/2023	Medium

If I use the Rating column from the Rating table in the legend, I get this:

On the data model side, the Calendar Date joins the Risk rating and the Risk table, and the Risk Rating actively joins the Risk table on the Risk Id.

Any help is much appreaciated.

[amitchandak]

@Sonnet , I think calendar should join to risk Rating and Risk should also join to risk rating

Try meausre like

Count of Risks by Historical Rating =
CALCULATE(
COUNTROWS('Rating'),
FILTER(
'Rating Rating',
'Rating Rating'[Risk Id] = 'Rating'[Risk Id]
&& 'Rating Rating'[Semester Date] <= MAX('Calendar Table'[Date])
)
)

[Sonnet]

When using your measure above, at this point:
'Rating Rating'[Risk Id] = 'Rating'[Risk Id]

the Rating[Risk Id] is not selectable. Only measures are displayed.

Any other way to solve it please?