Monthly Snapshot reporting

Appreciate some assistance in a monthly snapshot reporting. Attached the pbix and also a sample excel file. Note that my original data source is SQL. The data is entered thru via a powerapp and recorded in SQL and visualised through the PBI. I have attached an excel file and given a summary of the problem and expected results. Struggling to create the DAX for this.

Problem: Need the risks to be carried over to the months, except when in Closed Status.
Risk1: Reported in Jan’23 with Open Status
Risk1: Status Changed in Mar’23 to Pending Status

In PowerBI,
When i click on Jan, i need to see Risk1 which has Open Status
When i click on Feb, i need to see Risk1 which also has Open Status since no change was done.
When i click on Mar, i need to see Risk1 as Pending

So each month a snapshot is taken and risks with (any status other than Closed) is carried over to the next month.
In the sample data that i have provided, i have noted “My Comments” in Column K on tab “Problem and Output”. Note expected results from Column M onwards.

Please let me know if you require any further information.
Many Thanks, Jyo

Risk Register.xlsx (22.4 KB)
Actions_Test.pbix (67.9 KB)

Hi, I have got the solution. Please close this.

Hi @Jyo

We are glad you found a solution to your issue, it would be nice if you can post your solution to the problem so others can learn too.