Creating a mapping table based on date completed

To create a mapping table in Power BI that links status reports with items from the work table based on the date completed, you can use DAX to define a calculated table. Below is an example of how you can structure the DAX expression:

MappingTable =
VAR FilteredWorkTable =
FILTER(
'WorkTable',
'WorkTable'[Date Completed] >= 'StatusTable'[Reporting Start] &&
'WorkTable'[Date Completed] <= 'StatusTable'[Reporting End]
)
RETURN
ADDCOLUMNS(
'StatusTable',
"StatusReportID", 'StatusTable'[ID],
"WorkItemID", 'FilteredWorkTable'[ID]
)

In this DAX expression:

• 'WorkTable' and 'StatusTable' are assumed to be the names of your work table and status report table, respectively. You'll need to replace these with the actual table names in your Power BI model.
• 'WorkTable'[Date Completed] refers to the column in the work table that contains the date when the work was completed.
• 'StatusTable'[Reporting Start] and 'StatusTable'[Reporting End] are assumed to be the columns in the status report table that contain the start and end dates for the reporting period.
• FILTER function filters the work table to include only rows where the date completed falls within the reporting start and end dates of the status report.
• ADDCOLUMNS function adds two new columns to the resulting table: "StatusReportID" which contains the ID of the status report, and "WorkItemID" which contains the ID of the work item from the filtered work table.

You can create this calculated table in Power BI by going to the Modeling tab, clicking on New Table, and then entering the DAX expression provided above.

Once the mapping table is created, you can use it to relate status reports to work items in your Power BI reports.

If this post helps, then please consider Accepting it as the solution to help the other members find it more quickly.

In case there is still a problem, please feel free and explain your issue in detail, It will be my pleasure to assist you in any way I can.