Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

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
Employee
Employee

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
Employee
Employee

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


@mahoneypat Thank you!!

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.