Hello, I’m looking for assistance with a DAX calculation.
It’s the Dashboard for Project Status. The connection will be a Folder connection, and for each month, I will upload a file. I now have three months’ worth of data that shows the status of each project for each month. The “Connecticut Command - Infrastructure” project, for example, includes status on each source file, such as “Green” in January 2022, “Yellow” in February 2022, and “Red” in March 2022. It also features a Date column (total of 5 columns) for these projects that capture the Baseline Preliminary Date, as well as several additional Baseline Date columns.
Baseline Preliminary Date =
VAR PeriodName =
IF (
HASONEVALUE ( ‘Data Dump’[Project Name] ),
CALCULATE (
MIN ( ‘Data Dump’[Period] ),
ALLEXCEPT ( ‘Data Dump’, ‘Data Dump’[Project Name] )
)
)
VAR ProjectName =
SELECTEDVALUE ( ‘Data Dump’[Project Name] )
VAR V =
CALCULATE (
SELECTEDVALUE ( ‘Data Dump’[Baseline Preliminary Date] ),
ALLEXCEPT ( ‘Data Dump’, ‘Data Dump’[Project Name] ),
KEEPFILTERS (
TREATAS (
{ ( PeriodName, ProjectName ) },
‘Data Dump’[Period],
‘Data Dump’[Project Name]
)
)
)
RETURN
IF ( ISBLANK ( V ), " - ", Format(V,“DD-MM-YYYY”) )
Now that I have a few slicers on the Report page, the above DAX works, but I need to update the code to include the following new logic. I’d like to add another filter (inside the code) with the new criteria being column “Current Macro Phase” NOT Equals Preliminary and provided code.
If a project starts in January but the Current Macro Phase is “Preliminary,” my DAX measure should show the Baseline Date from the next month’s file (Considering the “Current Macro Phase”(Column in Data Set) is been changed in February Data file). If not then it should look for the Source where these criteria meet.
Test Report.pbix (65.1 KB)
Project_Status_GCS, Budget & PMO_05-01-2022.xlsx (100.2 KB)
Project_Status_GCS, Budget & PMO_05-02-2022.xlsx (100.3 KB)
Project_Status_GCS, Budget & PMO_05-03-2022.xlsx (100.2 KB)
