Hi,
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.
Example:
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)