cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
PowerBI123456
Post Partisan
Post Partisan

Comparing 2 dates

Hi - I have 2 tables, notes and payments on an account. I am trying to determine which accounts had a payment after the max note date. Here is the sample file: Sample File 

 

Here is the notes table 

PowerBI123456_2-1614295172559.png

Here is the payments table:

 

PowerBI123456_3-1614295188242.png

 

In this example, accounts 1 and 2 had a payment after the max note date and not account 3. 

 

Here is my model. This is simplied, but I am dealing with millions of rows so ideally if this can be done in a measure that would be great. Appreciate the help!

 

PowerBI123456_4-1614295314919.png

 

1 ACCEPTED SOLUTION
mahoneypat
Super User
Super User

Here is a measure that shows one way to do it.

 

Accts Late Payments =
VAR vSummary =
    ADDCOLUMNS (
        VALUES ( 'DIM: Account Numbers' ),
        "cMaxNote",
            CALCULATE (
                MAX ( 'FACT: Notes'[Note Date] )
            )
    )
RETURN
    COUNTROWS (
        FILTER (
            vSummary,
            VAR vThisMax = [cMaxNote]
            RETURN
                CALCULATE (
                    COUNTROWS ( 'FACT: Payment' ),
                    'FACT: Payment'[Payment Date] > vThisMax
                ) >= 1
        )
    )

Pat





Did I answer your question? Mark my post as a solution! Kudos are also appreciated!

To learn more about Power BI, follow me on Twitter or subscribe on YouTube.


@mahoneypa HoosierBI on YouTube


View solution in original post

2 REPLIES 2
mahoneypat
Super User
Super User

Here is a measure that shows one way to do it.

 

Accts Late Payments =
VAR vSummary =
    ADDCOLUMNS (
        VALUES ( 'DIM: Account Numbers' ),
        "cMaxNote",
            CALCULATE (
                MAX ( 'FACT: Notes'[Note Date] )
            )
    )
RETURN
    COUNTROWS (
        FILTER (
            vSummary,
            VAR vThisMax = [cMaxNote]
            RETURN
                CALCULATE (
                    COUNTROWS ( 'FACT: Payment' ),
                    'FACT: Payment'[Payment Date] > vThisMax
                ) >= 1
        )
    )

Pat





Did I answer your question? Mark my post as a solution! Kudos are also appreciated!

To learn more about Power BI, follow me on Twitter or subscribe on YouTube.


@mahoneypa HoosierBI on YouTube


View solution in original post

@mahoneypat Thank you!!

Helpful resources

Announcements
Power BI December 2021 Update_carousel 768x460.jpg

Check it Out!

Click here to read more about the December 2021 Updates!

User Group Leader Meeting January 768x460.png

Calling all User Group Leaders!

Don't miss the User Group Leader meetings on January, 24th & 25th, 2022.

Jan 2022 Dev Camp 768x460 copy.png

Power BI Dev Camp- January 27th, 2022

Mark your calendars and join us for our next Power BI Dev Camp!

Top Solution Authors
Top Kudoed Authors