Dynamic date comparisons as filters

[knuckledown234]

For context, my model has two tables, Project and Draft.

Project has a list of Project #s, Draft #s and Project Dates. Note that the Draft numbers are unique.

Project	Project Date	Draft #
397	1/30/2022	1
397	1/30/2022	4
397	1/30/2022	5
397	1/30/2022	8
412	2/22/2023	2
412	2/22/2023	3
412	2/22/2023	10
671	5/15/2021	6
671	5/15/2021	7
671	5/15/2021	9
412	2/22/2023	12

The Draft table has a list of Draft #s and their status date when they enter statuses Submitted, Approved and Closed:

Draft #	Status	Stage	Status Date	Project
1	Submitted	66	12/20/2018	397
1	Approved	99	1/12/2019	397
1	Closed	99	1/29/2019	397
2	Submitted	66	12/1/2019	412
2	Approved	99	12/21/2019	412
2	Closed	99	2/6/2020	412
3	Submitted	66	6/1/2020	412
3	Approved	99	7/17/2020	412
3	Closed	99	7/8/2020	412
4	Submitted	66	8/14/2020	397
4	Approved	99	9/18/2020	397
4	Closed	99	10/14/2020	397
5	Submitted	66	6/15/2021	397
5	Approved	99	7/28/2021	397
5	Closed	99	8/19/2021	397
6	Submitted	66	9/12/2021	671
6	Approved	99	12/1/2021	671
6	Closed	99	11/8/2021	671
7	Submitted	66	1/1/2023	671
7	Approved	99	2/16/2023	671
7	Closed	99	3/14/2023	671
8	Submitted	66	4/2/2023	397
8	Approved	99	5/9/2023	397
8	Closed	99	5/10/2023	397
9	Submitted	66	8/30/2023	671
9	Approved	99	9/5/2023	671
9	Closed	99	9/20/2023	671
10	Submitted	66	5/17/2023	412
10	Approved	99	6/5/2023	412
10	Closed	99	6/28/2023	412
12	Submitted	66	8/1/2021	412

My data model has a physical relationship from Project (1) to Draft (many) on the Draft # field. (Project 1:*Draft)

I am planning on building this as a dataset that I will combine with a larger dataset as a composite model, so I do not have a date table (because the larger dataset already has a date table).

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

• The user is only interested in projects with Submission dates between 1/1/2019 - 12/31/2021, closure dates between 1/1/2019 - 12/31/2021, and Approval Dates between 1/1/2019 – 12/31/2021. The user might want to be able to change these dates, for example with a slicer. The user is less interested in constraining the project date.
• 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	Most Recent Approval	Most Recent Closure	Most Recent Submission	Project Date	Days from Most Recent Approval to Project Date
671	12/1/2021	11/8/2021	9/12/2021	5/15/2021	-200

• You might notice that only one project is on this table! That is because:
  • 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 on the Draft table for “Most Recent Approval Date,” “Most Recent Submission Date,” etc.

In Power BI, I created a table visual and dropped in the Project table's Project #.

I then dropped in measures for highest approval date, highest closure date, highest submission date, project date, and days between most recent approval and project date. These are those measures:

Most Recent Submission Date =

CALCULATE(

MAX('Draft Table'[Status Date])

, 'Draft Table'[Status] in {"Submitted"}

)

Most Recent Approval Date =

CALCULATE(

MAX('Draft Table'[Status Date])

, 'Draft Table'[Status] in {"Approved"}

)

Most Recent Closure Date =

CALCULATE(

MAX('Draft Table'[Status Date])

, 'Draft Table'[Status] in {"Closed"}

)

Project Date =

VAR UniqueID = MAX('Draft Table'[Draft #])

RETURN

CALCULATE(

    MAX('Project'[Project Date])

    , 'Project'[Draft #] IN {UniqueID}

)

Days Between Most Recent Approval and Project Date =

DATEDIFF(

    [Most Recent Approval Date]

    , [Project Date]

    , DAY

)

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

I feel like I'm not thinking about this correctly, perhaps because I'm building this based off of a SQL query.

The SQL query has two CTEs (one for finding the most recent approval dates on projects within the user's requested date range, the second CTE for finding the most recent closure dates on projects within the user's requested date range). The SQL query then joins the the two CTEs to a main query that finds the most recent submission dates on projects within the user's requested date range. The person running the SQL query just changes the dates whenever the user makes a new request, but we're hoping to make the Power BI dashboard more self-service so the user can select those dates. However, I'm not sure how to make the dates easy to filter while also building the table that the user wants.

What are your thoughts? Am I approaching this problem in the wrong way?

[knuckledown234]

I'd like to circle back to thank @amitchandak and @v-yohua-msft for their contributions to this topic! My original post was very vague for such a complex scenario. To make things even more complex, my user changed their parameters a couple of times, too.

@amitchandak's tutorials on LASTDATE() were thought-provoking, thank you so much!

@v-yohua-msft's suggestion to include a Date table and write a nested series of formulas was very helpful. Because I felt the solution I ultimately chose was similar to @v-yohua-msft, I am giving him kudos.

Here is what I ultimately did.

• 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 as a composite dataset, I resisted using a date table. However, I think adding the date table and placing 'Date'[Date] into the date selection slicer per v-yohua-msft's suggestion helped me solve the problem.

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! Thank you both again for all of your help!

[v-yohua-msft · Accepted solution]

Hi,   @knuckledown234 

Based on your information, I create two tables.

At the same time, I also created a date table with the minimum State Date as the starting point and the maximum State Date as the end point.

Date = ADDCOLUMNS(CALENDAR(MIN('Draft Table'[Status Date]),MAX('Draft Table'[Status Date])),"year",YEAR([Date]))

Here are my thoughts:

Create a date table and form a one-to-many relationship with Start Date.

Then create a measure, using the following DAX

 Logo = CALCULATE(COUNTAX( FILTER('Draft Table',MIN('Draft Table'[Status Date])<=MIN('Date'[Date])&&MIN('Date'[Date])<=MAX('Draft Table'[Status Date])|| MAX('Date'[Date])>=MAX('Draft Table'[Status Date])&&MAX('Date'[Date])<=MAX('Draft Table'[Status Date])&&[Most Recent Submission Date]<=[Most Recent Closure Date]&&[Most Recent Closure Date]<=[Most Recent Approval Date] ||(MIN('Draft Table'[Status Date])<=MIN('Date'[Date])&&MIN('Date'[Date])<=MAX('Draft Table'[Status Date]))&&[Most Recent Submission Date]<=[Most Recent Closure Date]&&[Most Recent Closure Date]<=[Most Recent Approval Date] ||(MIN('Draft Table'[Status Date])<=MAX('Date'[Date])&&MAX('Date'[Date])<=MAX('Draft Table'[Status Date]))&&[Most Recent Submission Date]<=[Most Recent Closure Date]&&[Most Recent Closure Date]<=[Most Recent Approval Date]),('Draft Table'[Project])), CROSSFILTER('Draft Table'[Status Date],'Date'[Date],None)) 

I'm using this measure to determine the required date, when the minimum value of the slicer >=the minimum value of the date table &&< the minimum value of the date table &gt<=closed<=approve or the maximum value of the slicer >=the minimum value of the date table && the maximum value of the date table< = the maximum value of the date table && &<&=closed<=approve, the maximum and minimum values of the data table are within the range of the minimum and maximum values of the slicer.

Next, create a slicer and put the Date in the Date table into the Filed.

Here is my preview:

How to Get Your Question Answered Quickly 

If it does not help, please provide more details with your desired output and pbix file without privacy information (or some sample data) .

Best Regards

Yongkang Hua

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[knuckledown234]

I would like to make sure I understand your FILTER conditions. I'm a little confused about how they are grouped together, since some of them seem to repeat and only a couple are "grouped" together in parentheses.

Condition 1: the earliest Status Date must be on/before the earliest date selection on the slicer
AND C2: the most recent Status Date must be on/after the most earliest date selection on the slicer
OR C3: the most recent Status Date must be on/before the most recent date selection on the slicer
AND C4: the most recent Status Date must be on/after the most recent date selection on the slicer
AND C5: Most recent submission date must be on/before the most recent closure date
AND C6: Most recent closure date must be on/before the most recent approval date
OR Group of Conditions A:
Group of Conditions A, C1: Earliest status date must be on/before earliest slicer selection
AND Group of Conditions A, C2: Earliest status date must be on/before most recent slicer selection

End Group of Conditions A
AND C7: Most recent submission date must be on/before the most recent closure date
AND C8: Most recent closure date must be on/before the most recent approval date

OR (Begin Group of Conditions B
Group of Conditions B, C1: Earliest status date must be on/before most recent slicer selection
AND Group of Conditions B, C2: Most recent slicer selection must be on/before most recent status date

End GRoup of Conditions B
AND C9: Most recent submission date must be on/before the most recent closure date
AND C10: Most recent closure date must be on/before the most recent approval date

Logo =
CALCULATE (
    COUNTAX (
        FILTER (
            'Draft Table',
            MIN ( 'Draft Table'[Status Date] ) <= MIN ( 'Date'[Date] )
// Condition 1: the earliest Status Date must be on/before the earliest date selection on the slicer
                && MIN ( 'Date'[Date] ) <= MAX ( 'Draft Table'[Status Date] ) 
// Condition 2: the most recent Status Date must be on/after the most earliest date selection on the slicer
                || MAX ( 'Date'[Date] ) >= MAX ( 'Draft Table'[Status Date] ) 
// C3: the most recent Status Date must be on/before the most recent date selection on the slicer
                    && MAX ( 'Date'[Date] ) <= MAX ( 'Draft Table'[Status Date] ) 
// C4: the most recent Status Date must be on/after the most recent date selection on the slicer
                    && [Most Recent Submission Date] <= [Most Recent Closure Date]
// C5: Most recent submission date must be on/before the most recent closure date
                    && [Most Recent Closure Date] <= [Most Recent Approval Date] 
// C6: Most recent closure date must be on/before the most recent approval date
                || ( // Beginning of group of conditions A
                    MIN ( 'Draft Table'[Status Date] ) <= MIN ( 'Date'[Date] )

// Group of Conditions A, C1: Earliest status date must be on/before earliest slicer selection
                        && MIN ( 'Date'[Date] ) <= MAX ( 'Draft Table'[Status Date] )

// Group of Conditions A, C2: Earliest status date must be on/before most recent slicer selection

                ) // End group of conditions A
                    && [Most Recent Submission Date] <= [Most Recent Closure Date]

// C7: Most recent submission date must be on/before the most recent closure date
                    && [Most Recent Closure Date] <= [Most Recent Approval Date]

// C8: Most recent closure date must be on/before the most recent approval date
                || ( // Begin Group of Conditions B
                    MIN ( 'Draft Table'[Status Date] ) <= MAX ( 'Date'[Date] )

// Group of Conditions B, C1: Earliest status date must be on/before most recent slicer selection
                        && MAX ( 'Date'[Date] ) <= MAX ( 'Draft Table'[Status Date] )

// Group of Conditions B, C2: Most recent slicer selection must be on/before most recent status date
                ) // End GRoup of Conditions B
                    && [Most Recent Submission Date] <= [Most Recent Closure Date]

// C9: Most recent submission date must be on/before the most recent closure date
                    && [Most Recent Closure Date] <= [Most Recent Approval Date]

// C10: Most recent closure date must be on/before the most recent approval date        ),
        ( 'Draft Table'[Project] )
    ),
    CROSSFILTER ( 'Draft Table'[Status Date], 'Date'[Date], NONE )
)

[amitchandak]

@knuckledown234 , check if calculation of data on latest date can help

https://amitchandak.medium.com/power-bi-get-the-last-latest-value-of-a-category-d0cf2fcf92d0

https://amitchandak.medium.com/power-bi-get-the-sum-of-the-last-latest-value-of-a-category-f1c839ee8...