Creating a mapping table based on date completed

[mhomol]

I have a table for generating status reports.  Users can provide the date range (Reporting Start and Reporting End) of the status report and provide some contextual data, like executive summaries or analysis.  Ultimately, there are details that get pulled into this report that represent the work completed between the Reporting Start and End fields.  Users can make as many status reports as they want for date ranges, meaning that multiple status reports should be able to display the same details, if needed.  I want the details to simply show up when a status report is selected in Power BI.  I need to create a mapping table that will give me the ID of the status report table plus the ID's of any items from the work table where Date Completed is between the Reporting Start and End from the status table.  Can someone help me with the DAX structure for this table?  I can't visualize it.

[mhomol]

@123abc , I really appreciate your response.  Unfortunately, the above is not working for me.  If I try the statement as you wrote it but with my table names and columns, I get a syntax error because it can't find the 2nd table.

I know how I would do this in a non-DAX world: I would loop through all rows in the status report table, then for each row in that table find all the matches in the customer board table based on what falls in the date range.  But I can't wrap my head around how to do this in DAX.  I would also be up for doing this in Power Query M if that would be easier.  The big reason I need to have this mapping table is that a single row from the customer board table could appear in multiple status reports, depending on a date range.  For instance, you could make a monthly status report then a quarterly one, expecting the board item to show up on both.  Make sense?

[123abc]

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.