DAX - Power BI - Creating Historical Table from Audit Table

Question

I'm trying to create a MonthEndStatus Historical table from the data in my Audit table. This table does not exist and we need to create it from the audit table records.

I have an Audit table that contains changes to Submission Records.

SubID	RevID	Status	ChangeDate
12	1	New	1/2/2021
13	1	New	1/3/2021
12	2	Open	1/3/2021
13	2	Open	1/5/2021
12	3	Closed	4/1/2021
12	4	Re-Open	6/1/2021
12	5	Closed	9/2/2021
12	6	Open	9/3/2021
13	3	Closed	9/5/2021

I also have the standard date table with all the good Date data for each day.

I want the MonthEndStatus to look like

SubID	Status	Month End
12	Open	1/2021
13	Open	1/2021
12	Open	2/2021
13	Open	2/2021
12	Open	3/2021
13	Open	3/2021
12	Closed	4/2021
13	Open	4/2021
12	Closed	5/2021
13	Open	5/2021
12	Re-Open	6/2021
13	Open	6/2021
12	Re-Open	7/2021
13	Open	7/2021
12	Re-Open	8/2021
13	Open	8/2021
12	Open	9/2021
13	Closed	9/2021

My first thought was to use a Left Outer Merge from the Date table to the Audit table but it doesn't create a row for each ID for each Month.

How can I use Power BI DAX or other method to create the records for the months that are NOT in the Audit table and use the value from the previous month?

Thanks, in advance, for your assistance.

matt

jprzd jprzd · Accepted Answer · 2021-06-02T01:27:53

MonthEndStatus = 
    VAR EndOfMonthDates =
        TREATAS (
            SELECTCOLUMNS (
                ADDCOLUMNS (
                    VALUES ( Dates[MonthName] ),
                    "Date", CALCULATE ( MAX ( Dates[Date] ) )
                ),
                "Date", [Date]
            ),
            Dates[Date]
        )
RETURN
ADDCOLUMNS (
GENERATE ( VALUES ( Submissions[SubID] ), EndOfMonthDates ),
    "Status",
        VAR CurrentEndOfMonth = Dates[Date]
        VAR MAXSubbmissionDateInRange =
            CALCULATE (
                MAX ( Submissions[ChangeDate] ),
                REMOVEFILTERS ( Dates ),
                Submissions[ChangeDate] <= CurrentEndOfMonth
            )
        RETURN
            CALCULATE (
                SELECTEDVALUE ( Submissions[Status] ),
                REMOVEFILTERS ( Dates ),
                Submissions[ChangeDate] = MAXSubbmissionDateInRange
            )
)

DAX - Power BI - Creating Historical Table from Audit Table

1 Answers