Measure that will dynamically calculate status for any time period in the past based on date columns

[BrandyBurk]

I'm hoping someone can help me out.  I've wasted 2 days trying to get past this. I'm trying to create a measure to hold the status of my project as it is today OR as it was in the past.  Ultimately I'm trying to count the number of projects that were in each phase for any point in time.  

I have two tables.  One is my calendar date table called "Calendar" with 4 columns:

• [Date]
• [Year]
• [Quarter]
• [Month]  

The other table is my project data table called "Projects".  In my projects table I have the Project ID and 4 date columns that track when the project moves to various phases which are:

• Project Approved Date (status moves from null or blank to "Planning")
• Project Start Date (status moves from "Planning" to "In Progress")
• Project Complete Date (status moves from "In Progress" to "Pending Review")
• Project Closeout Date (status moves from "Pending Review" to "Complete")

As far as relationships go....[Project Approved Date] has an active relationship with my calendar table and all the rest of the date fields have an inactive relationship with my calendar table.

Here is the result when I put the fields in a matrix.  I've got my 'Calendar'[Year] in the column, [Project ID] in the row and the Status measure in the values well but it's not working correctly as you can see.  

Here is the DAX statement:

Status =
VAR MaxDate = MAX('Calendar'[Date])
RETURN
    IF(
        CALCULATE(MAX('Projects'[Project Closeout Date]), USERELATIONSHIP('Projects'[Project Closeout Date], 'Calendar'[Date])) <= MaxDate,
        "Complete",
        IF(
            CALCULATE(MAX('Projects'[Project Complete Date]), USERELATIONSHIP('Projects'[Project Complete Date], 'Calendar'[Date])) <= MaxDate,
            "Pending Closeout",
            IF(
                CALCULATE(MAX('Projects'[Project Start Date]), USERELATIONSHIP('Projects'[Project Start Date], 'Calendar'[Date])) <= MaxDate,
                "In Progress",
                IF(
                    MAX('Projects'[Project Approved Date]) <= MaxDate,
                    "Planning",
                    BLANK()
                )
            )
        )
    )

Here is my data:

Project ID	Project Approved Date	Project Start Date	Project Complete Date	Project Close Out Date
1	1/14/2020	1/14/2020	3/24/2020	3/24/2020
2	1/14/2020	1/14/2020	3/24/2020	3/24/2020
3	1/14/2020	1/14/2020	3/24/2020	3/24/2020
4	1/14/2020	1/14/2020	3/24/2020	3/24/2020
5	10/28/2020	10/28/2020	7/19/2021	7/19/2021
6	9/7/2022	9/7/2022	10/14/2022	10/14/2022
7	3/10/2023	3/10/2023	6/28/2023	6/28/2023
8	6/19/2023	6/19/2023	12/31/2023	12/31/2023
9	3/24/2023	3/24/2023	3/29/2023	3/29/2023
10	6/5/2023	6/5/2023	6/30/2023	6/30/2023
11	6/15/2023	6/15/2023
12	11/15/2023	11/15/2023
13	11/8/2023	11/8/2023	12/31/2023	12/21/2023
14	11/16/2023	11/16/2023	12/31/2023	12/31/2023
15	6/22/2023	6/22/2023	1/31/2024	1/31/2024
16	11/16/2023
17	1/22/2024	1/22/2024
18	11/7/2023	11/7/2023	3/31/2024	3/31/2024
19	11/7/2023	11/7/2023
20	11/7/2023	11/7/2023
21	1/26/2024	1/26/2024
22	10/12/2023	10/12/2023	4/1/2024
23	10/12/2023	10/12/2023	1/31/2024	1/31/2024
24	10/12/2023	10/12/2023	10/12/2023	10/12/2023
25	1/16/2024	1/16/2024
26	4/11/2022	4/11/2022	4/11/2022	4/11/2022
27	8/15/2022	8/15/2022	8/15/2022	8/15/2022
28	7/31/2022	7/31/2022	7/31/2022	7/31/2022
29	9/30/2022	9/30/2022	9/30/2022	9/30/2022
30	9/30/2022	9/30/2022	12/31/2022	12/31/2022
31	10/1/2022	10/1/2022	12/31/2022	12/31/2022
32	12/1/2022	12/1/2022	12/31/2022	12/31/2022
34	9/30/2021	9/30/2021	10/31/2021	10/31/2021
35	9/30/2021	9/30/2021	10/31/2021	10/31/2021
36	9/1/2021	9/1/2021	9/30/2021	9/30/2021
37	9/1/2021	9/1/2021	9/30/2021	9/30/2021
38	8/1/2020	8/1/2020	1/15/2021	1/15/2021
39	11/1/2020	11/1/2020	12/4/2020	12/4/2020
40	8/1/2020	8/1/2020	9/1/2020	9/1/2020
41	3/5/2021	3/5/2021	3/31/2021	3/31/2021
42	9/24/2021	9/24/2021	9/30/2022	9/30/2022
43	9/24/2021	9/24/2021	4/30/2022	4/30/2022
44	9/24/2021	9/24/2021	9/30/2022	9/30/2022
45	9/24/2021	9/24/2021	9/30/2022	9/30/2022
46	9/24/2021	9/24/2021	12/31/2021	12/31/2021
47	9/24/2021	9/24/2021	12/31/2021	12/31/2021
48	9/24/2021	9/24/2021	12/31/2021	12/31/2021
49	9/24/2021	9/24/2021	12/31/2021	12/31/2021
50	12/12/2022	12/12/2022	12/31/2022	12/31/2022
51	12/12/2022	12/12/2022	12/31/2022	12/31/2022
52	3/15/2024	4/25/2024

[DataNinja777 · Accepted solution]

Hi @BrandyBurk ,

IMO, for fact tables with multiple date columns where you want to visualize the duration of the project, it is easier to perform flexible analysis if your calendar table is a disconnected table from your fact table.  

For example, I created a data model with disconnected tables like below using your data as the fact table.  

Then I created measures which for the duration of the period the current status of the project will be shown like below.  Where project is approved and not completed, show letter "A" for approval.  

If the project is started and not completed, the letter "S" is shown to indicate started.  

If the project is completed and not closed out yet (although most of them are on the same dates), the letter "C" is shown to indicate completed.  

If the project is closed out, "X" is shown like below:

Then these 4 measures were combined in a text measure below, and when put in the matrix with the time dimension field in the column, you can visualize the duration of the project statuses.  

You can further tweak the measures above to be in line with your required output.  I attach an example pbix file. 

Best regards,

[BrandyBurk]

THANKS!!!  This is what I needed!

[BrandyBurk]

I have made progress but it's not the best scenario.  In case anyone else struggles with this, here's the solution and what the issue was.

The measure was not evaluating the status for each year independently within the context of my matrix. To ensure that the measure calculates the status correctly for each year, I had to adjust the measure to respect the filter context imposed by the matrix visual.

In a matrix, each cell represents a unique combination of the row and column groupings, and the calculations within each cell are subject to the filter context defined by those groupings. I had to make sure that the measure was calculating the status based on the maximum date within each respective year.  Not the best news as I like using Date Hierarchies in my visuals and this issue won't let me do that.  Will keep playing with it....if anyone has advice, would very much appreciate it!

Here's a revised version of the measure that works for an annual time period within a matrix visual and the resulting matrix screenshot.

Status = 
VAR SelectedEndDate = MAX('Calendar'[Date])
VAR CurrentYear = SELECTEDVALUE('Calendar'[Year])
VAR EndOfCurrentYear = DATE(CurrentYear, 12, 31)

VAR MaxRemediationReviewDate = CALCULATE(MAX('Event Tracking'[Remediation Review Date]), ALL('Calendar'), USERELATIONSHIP('Event Tracking'[Remediation Review Date], 'Calendar'[Date]))
VAR MaxRemediationCompleteDate = CALCULATE(MAX('Event Tracking'[Remediation Complete Date]), ALL('Calendar'), USERELATIONSHIP('Event Tracking'[Remediation Complete Date], 'Calendar'[Date]))
VAR MaxRemediationStartDate = CALCULATE(MAX('Event Tracking'[Remediation Start Date]), ALL('Calendar'), USERELATIONSHIP('Event Tracking'[Remediation Start Date], 'Calendar'[Date]))
VAR MaxDiscoveryDate = CALCULATE(MAX('Event Tracking'[Discovery Date]), ALL('Calendar')) 

VAR StatusAnnual =
    SWITCH(
        TRUE(),
        NOT(ISBLANK(MaxRemediationReviewDate)) && MaxRemediationReviewDate <= EndOfCurrentYear, "Complete",
        NOT(ISBLANK(MaxRemediationCompleteDate)) && MaxRemediationCompleteDate <= EndOfCurrentYear, "Implemented, Pending Review",
        NOT(ISBLANK(MaxRemediationStartDate)) && MaxRemediationStartDate <= EndOfCurrentYear, "In-Progress",
        NOT(ISBLANK(MaxDiscoveryDate)) && MaxDiscoveryDate <= EndOfCurrentYear, "Planning",
        BLANK() // Return a blank value if none of the conditions are met
    )

RETURN
StatusAnnual

[BrandyBurk]

Well I tried out SWITCH and got the EXACT same results.  Then I tried to step through the process with one status at a time and it messes up as soon as I add the next test.  I'm just flabbergasted.  Usually I can figure this stuff out after some time but I just don't see what is wrong!  Here is the SWITCH DAX below. 

Status =

VAR SelectedEndDate = MAX('Calendar'[Date])

VAR MaxRemediationReviewDate = CALCULATE(MAX('Event Tracking'[Remediation Review Date]), USERELATIONSHIP('Event Tracking'[Remediation Review Date], 'Calendar'[Date]))

VAR MaxRemediationCompleteDate = CALCULATE(MAX('Event Tracking'[Remediation Complete Date]), USERELATIONSHIP('Event Tracking'[Remediation Complete Date], 'Calendar'[Date]))

VAR MaxRemediationStartDate = CALCULATE(MAX('Event Tracking'[Remediation Start Date]), USERELATIONSHIP('Event Tracking'[Remediation Start Date], 'Calendar'[Date]))

VAR MaxDiscoveryDate = CALCULATE(MAX('Event Tracking'[Discovery Date]), USERELATIONSHIP('Event Tracking'[Discovery Date], 'Calendar'[Date]))

RETURN

SWITCH(

    TRUE(),

    MaxRemediationReviewDate <= SelectedEndDate, "Complete",

    MaxRemediationCompleteDate <= SelectedEndDate, "Implemented, Pending Review",

    MaxRemediationStartDate <= SelectedEndDate, "In Progress",

    MaxDiscoveryDate <= SelectedEndDate, "Planning",

    BLANK()

)

[AndyEagleton]

What you need to do to debug this is start with one status and make sure you get that right rather than combining them all together in a single measure. Watch out for blanks. Make sure these are handled correctly.

[AndyEagleton]

The DAX could be correct here though SWITCH might be better than nested IFs. The screen shot does not match the DAX. The DAX clearly has hyphen in 'In-Progress' whereas the screenshot does not.

[BrandyBurk]

Here's an example of what the correct results would be: