I’m working with Power BI to analyze work package (WP) data over time. My dataset includes an initial bulk export of all WPs followed by daily exports that only include WPs with changes. Each record is tied to an Export_Date. I need to be able to:
- View the most recent record for every unique WP up to and including the selected Export_Date using a slicer.
- Ensure that all WPs are shown, even if they haven’t changed on or after the selected Export_Date.
- The WP data should reflect the state of the WP at any given point in time, based on the historical Export_Date.
Key Requirements:
- The slicer allows me to select any Export_Date, and the data should show the latest available record for each unique WP up to and including that date.
- If a WP hasn’t changed recently, I still need to see its last known state from previous export dates.
Data Structure:
- Initial bulk export: Contains all WPs as of a certain point in time.
- Daily exports: Only include WPs that have been modified on that day.
What I Have Tried:
- I’ve created a variable to capture the maximum export date for each WP up to and including the selected Export_Date from the slicer.
- I’ve used SUMMARIZE to group by WPNO_I and calculate the latest export date for each WP.
- I’ve used TREATAS, LOOKUPVALUE, and FILTER to filter the fact table and retrieve the most recent record per WP.
Current DAX Formula ive used:
KPI - Total WP =
VAR SelectedExportDate =
MAX ( WP_Fact_Qry[Export_Date] ) -- Get the selected export date from the slicer
VAR LatestExportDatePerWP =
SUMMARIZE (
WP_Fact_Qry,
WP_Fact_Qry[WPNO_I],
-- Group by work package
"Latest Export Date",
CALCULATE (
MAX ( WP_Fact_Qry[Export_Date] ),
WP_Fact_Qry[Export_Date] >= SelectedExportDate -- Get the latest export date <= the selected date
)
)
RETURN
CALCULATE (
DISTINCTCOUNT ( WP_Fact_Qry[WPNO_I] ),
-- Count distinct work packages
TREATAS (
LatestExportDatePerWP,
WP_Fact_Qry[WPNO_I],
WP_Fact_Qry[Export_Date]
)
)
Issue:
The formula works but ignores WPs that haven’t changed on the selected export date. If a WP wasn’t updated on or after the selected date, it’s not being shown, but I need to see the most recent record for every WP at any point in time based on the selected export date.
Additional Information:
- Bulk export of WPs was done initially, followed by daily exports of only the changed WPs.
- I want to be able to see the state of any WP at any point in time, based on the historical export data.
Any help or suggestions on how to retrieve the latest record for each WP, even if they haven’t changed, based on a Export_Date slicer would be greatly appreciated!
a brief summary of the Table in question:
contains a list of work packages (WPs), with each row representing the state of a work package at a given export date. The WPNO_I field, while representing the work package, is not unique, as a work package may have multiple rows if changes occur in future exports. The table tracks key fields related to the status, dates, and KPIs of each work package at the time of export.
