Using Date Comparisons as a CALCULATE() filter

sekrus · January 30, 2024, 11:21pm

For context,
I have two tables, Project and Draft.
Project has a list of Project #s and Draft #s. Note that the Draft numbers are unique.
The Draft table has a list of Draft #s and their status date when they enter statuses Submitted, Approved and Closed:
The model has a physical relationship from Table (1) to Draft (many) on the Draft # field.
You can also see the model here: dummy data 01302024 1651.pbix (68.7 KB)

User request:
The user would like to see the following:

The user is only interested in Status Dates between 1/1/2019 – 12/31/2021
The user would like to see a table like below, with the project number, the project date (from the project table), the most recent approval status date, the most recent closure status date, the most recent submitted status date, and the number of days from the most recent approval status date to the project date:

Project; 671
Most Recent Approval: 12/1/2021
Most Recent Closure: 11/8/2021
Most Recent Submission: 9/12/2021
Project Date: 5/15/2021
Days from Most Recent Approval to Project Date: -200

Also, in order for a project to be included on the table, the most recent closure date associated with any project must be BEFORE that project’s most recent approval date (within the above date filters)
Finally, in order for a project to be included on the table, the most recent submission date associated with any project must be BEFORE that project’s most recent closure date (within the above date filters)

For my date filter, I have a slicer with the Draft table’s Status Date, since the user may wish to change the range of dates they wish to view. The user potentially wanting to change filter date range is also why I’m hesitant to build a calculated column for “Most Recent Approval Date,” “Most Recent Submission Date,” etc.

In Power BI, I created a table visual and dropped in Project #. I then dropped in measures for highest approval date, highest closure date, and highest submission date.

I am struggling to filter the table to the final two bullet points (where the project should only appear on the table if the Most Recent Approval Date must be more recent than the Most Recent Closure date, AND if the Most Recent Closure Date is more recent than the most recent Submission Date).

I’ve tried building a measure similar to this. My thought is that I would drop this measure into the filter pane and filter to project numbers that return an amount on the measure. However, it takes quite a long time to load on Power BI desktop with the actual data tables. It also doesn’t seem to be returning correct results on my current table.

Measure to Filter Table Visual =
VAR Project_Num = SELECTEDVALUE(‘PROJECT’[Project])

VAR Approve_date =
CALCULATE(
MAX(‘Draft Table’[Status Date])
, ‘Draft Table’[STATUS] in {“Approved”}
, Project[Project] IN {Project_Num}
)

VAR Close_date =
CALCULATE(
MAX(‘Draft Table’[Status Date])
, ‘Draft Table’[STATUS] in {“Closed”}
, Project[Project] IN {Project_Num}
)

VAR Submit_Date =
CALCULATE(
MAX(‘Draft Table’[Status Date])
, ‘Draft Table’[STATUS] in {“Submitted”}
, Project[Project] IN {Project_Num}
)

VAR ApproveGreaterThanClose =
CALCULATE(
DISTINCTCOUNT(‘Draft Table’[Draft #])
, ‘Project’[Project] IN {Project_Num}
, FILTER(
VALUES(‘Draft Table’)
, ‘Draft Table’ [Status] IN {“Approved”} && ‘Draft Table’[Status Date] > Close_date
)
)

VAR CloseGreaterThanSub =
CALCULATE(
DISTINCTCOUNT(‘Draft Table’[Draft #])
, ‘Project’[Project] IN {Project_Num}
, FILTER(
VALUES(‘Draft Table’)
, Submit_date < Close_date
)
)

RETURN CloseGreaterThanSub + ApproveGreaterThanClose

What are your thoughts?

Dummy data here: dummy data 01302024 1651.pbix (68.7 KB)

sekrus · February 23, 2024, 1:09pm

I’d like to circle back to discuss what I ended up doing for my project. When I didn’t get a response here, I also posted this on another forum which gave me some ideas that inspired my final solution (specifically, the other forum suggested adding a date table and also writing measures with use of functions such as ADDCOLUMNS()).

In a dataflow, I combined the Draft and Project tables into one table, Project_Draft_Join. If you don’t have premium license, you can use a native query to do this.
I added a date table. I think because I was planning on adding this dataset to another composite dataset, I resisted using a date table.
However, I think adding the date table and placing ‘Date’[Date] into the date selection slicer helped.

For my end result visuals, I created the following:

A “Between” style date slicer with ‘Date’[Date] thrown in
A table visual with the newly combined table’s “Project #” field tossed, as well as helper measures.

In the filter pane for the table visual, I placed this measure:

Project Matches User Requirements =

VAR Project_Num = SELECTEDVALUE(Project_Draft_Join’[PROJECT])

VAR Submit_Date = {[Most Recent Submit Date]}

VAR Approve_date = {[Most Recent Approve Date]}

Var Close_date_Measure = {[Most Recent Close Date]}

RETURN

IF(

COUNTROWS(Submit_date) > 0  

    && Close_date_Measure > Submit_Date

    && Close_date_Measure < Approve_Date

, CALCULATE(

    COUNT('Project_Draft_Join'[DRAFT #])

    ,  'Project_Draft_Join'[Status] in {"Submitted"} // Status is "Submitted"

    , 'Project_Draft_Join'[PROJECT] IN {Project_Num}

)

)

I added a filter to only show items where the value is NOT blank.

As an example of one of my helper measures that I placed in the table, please see the measure I created to see the most recent “Submitted” status on a project and the Project date associated with that Submitted Status. I dropped this into the table visual “Fields” list:

Most Recent Submit Date Measure =

CALCULATE(

MAX('Project_Draft_Join'[Status Date])

, 'Project_Draft_Join'[Status] in {"Submitted"}

, USERELATIONSHIP('Project_Draft_Join'[Status Date], 'Calendar'[Date])

)

This is a measure to see the most recent Approval, which I also dropped in the table visual:

Most Recent Approval Date =

CALCULATE(

LASTDATE('Project_Draft_Join'[Status Date])

, REMOVEFILTERS('Calendar'[Date]) // One of my user's new requests was that the Approval date NOT be bound by the same date filter as the submission date, but the Approval date must be AFTER the submission date and closed date

, 'Project_Draft_Join'[status] in {"Approved"}

)

This is the measure to find the most recent Project Date, which I also dropped in the table fields list:

Project Date for most Recent Submission Date on Project =

VAR MostRecentSubbed =

CALCULATE(

MAX('Project_Draft_Join'[Status Date])

, 'Project_Draft_Join'[Status] in {"Submitted"}

)

VAR DraftNum =

CALCULATE(

SELECTEDVALUE('Project_Draft_Join'[Draft #])

, 'Project_Draft_Join'[Status Date] = MostRecentSubbed

, 'Project_Draft_Join'[Status] in {"Submitted"}

)

RETURN

CALCULATE(

SELECTEDVALUE('Project_Draft_Join'[Project Date])

, 'Project_Draft_Join' [Draft #] = DraftNum

)

The user wants to see the number of days from the Project Date to the Approval Date. To obtain this, I wrote the following and dropped the measure in the table fields list:

PROJECT DATE MINUS MOST RECENT APPROVED MEASURE =

VAR MOSTRECENTAPPROVED = {[Most Recent Approval Date]}

VAR MostRecentProjectDate = {[Project Date for most Recent Submission Date on Project]}

return

DATEDIFF(

MOSTRECENTAPPROVED

, MostRecentProjectDate

, DAY

)

I also created two card visuals: One to hold a measure to count the projects, one to hold a median measure for days between renewal and approval.

To obtain the count, I wrote the following:

Count projects within parameters and return a total count at total line scope =

VAR Project_num = SELECTEDVALUE(‘Project_Draft_Join’[PROJECT])

VAR Submit_Date = {[Most Recent Submit Date]}

VAR Approve_date = {[Most Recent Approval Date]}

Var Close_date_Measure = {[Most Recent Closed Date]}

RETURN

IF(

ISINSCOPE('Project_Draft_Join'[PROJECT]) // if the current visual row has one project number, follow the next IF() statement

, IF(

    COUNTROWS(Submit_date) > 0  // At least one submission is found within the date range

        && Close_date_Measure > Submit_Date // Closure date is after submit date

        && Close_date_Measure < Approve_Date // Approval date is after closure date

    , CALCULATE(

        COUNT('Project_Draft_Join'[DRAFT #])

        ,  'Project_Draft_Join'[status] in {"Submitted"} // Status is "Submitted"

        , 'Project_Draft_Join'[PROJECT] IN {Project_num}

    )

), COUNTROWS( // This returns a total count for table visuals with a "Total" values row.

    FILTER(

        ADDCOLUMNS(

            SUMMARIZE(

                'Project_Draft_Join'

                , 'Project_Draft_Join'[PROJECT]

            ), "MARK", [Project Matches User Requirements]

        ), NOT ISBLANK([Mark])

)

)

)

To obtain the median, I used this formula:

Median Days from Approval to Project Date =

VAR Project_num = SELECTEDVALUE(‘Project_Draft_Join’[PROJECT])

VAR Submit_Date = {[Most Recent Submit Date]}

VAR Approve_date = {[Most Recent Approval Date]}

Var Close_date_Measure = {[Most Recent Closed Date]}

RETURN

IF(

ISINSCOPE('Project_Draft_Join'[PROJECT])

, IF(

    COUNTROWS(Submit_date) > 0  

        && Close_date_Measure > Submit_Date

        && Close_date_Measure < Approve_Date

    , MEDIANX(

        FILTER(

            VALUES('Project_Draft_Join'[PROJECT])

            , NOT ISBLANK([PROJECT DATE MINUS MOST RECENT APPROVED MEASURE])

                && NOT ISBLANK([Project Matches User Requirements])

        ), [PROJECT DATE MINUS MOST RECENT APPROVED MEASURE]

    )

), MEDIANX( // This returns a total median for table visuals with a "Total" values row.

    FILTER(

        VALUES('Project_Draft_Join'[PROJECT])

        , NOT ISBLANK([PROJECT DATE MINUS MOST RECENT APPROVED MEASURE])

            && NOT ISBLANK([Project Matches User Requirements])

    ), [PROJECT DATE MINUS MOST RECENT APPROVED MEASURE]

)

)

I used the above two measures in the cards only, where the path for “Totals” would display. When I added the above two measures to the table, they were too expensive for Power BI desktop to compute. For the table visual, I turned off “Totals” and used the more basic measures.

I hope this topic helps someone with a similar problem!