DAX: Return values by matching different dates of columns

[Anonymous]

Hi all,

I have two columns Waterfall Date and Year Month.
Waterfall Date starts from the current month Jul-2021 until Dec-2022.
Year Month only includes the latest year month Jul-2021.

I'm trying to create a measure that would always return DP dollar for row items that are +1 month in the future (Waterfall date = Aug-2021, Year Month = Jul-2021).

Thanks all!

[v-kelly-msft · Accepted solution]

Hi @Anonymous ,

Create a measure as below:

Measure =
IF (
    MONTH ( MAX ( 'Table'[Year Month] ) ) = 12,
    CALCULATE (
        SUM ( 'Table'[DP dollar] ),
        FILTER (
            ALL ( 'Table' ),
            YEAR ( 'Table'[waterfall date] )
                = YEAR ( 'Table'[Year Month] ) + 1
                && MONTH ( 'Table'[waterfall date] ) = 1
        )
    ),
    CALCULATE (
        SUM ( 'Table'[DP dollar] ),
        FILTER (
            ALL ( 'Table' ),
            YEAR ( 'Table'[waterfall date] ) = YEAR ( 'Table'[Year Month] )
                && MONTH ( 'Table'[waterfall date] )
                    = MONTH ( 'Table'[Year Month] ) + 1
        )
    )
)

And you will see:

For the related .pbix file,pls see attached.

Best Regards,
Kelly

Did I answer your question? Mark my post as a solution!

[lbendlin]

Please provide sample data in usable format (not as a picture) and show the expected outcome.