DAX Calculation for Prior Period Date Range

Hi mcornfield ,

After adding other dimensions, if you want to calculate the value for the same day 2 months ago, then try the following formula. For example when [2. Answer Date] column is "2021-8-30", then calculate the [1. # Achieved] from "2021-6-30".

The same day 2 months ago = 
var _end = EDATE ( MAX ( 'Table'[QuestionEndDate] ), -2 )
var _start = EDATE ( MAX ( 'Table'[QuestionEndDate] ), -4 )
var PreviousPeriod = EDATE ( MAX ( 'Table'[2. Answer Date] ), -2 )
var sub =
    CALCULATE (
        SUM ( 'Table'[1. # Achieved] ), 
        filter ( 
            ALL ( 'Table' ),
            'Table'[2. Answer Date].[Date] = PreviousPeriod
                && 'Table'[2. Answer Date] <> BLANK()
        )   
    )
var total = 
    CALCULATE (
        SUM ( 'Table'[1. # Achieved] ), 
        filter ( 
            ALL ( 'Table' ),
            'Table'[QuestionEndDate] > _start
                && 'Table'[QuestionEndDate] <= _end
        )    
    )
return 
    IF (
        ISFILTERED ( 'Table'[2. Answer Date] ),
        sub,
        total
    )

If you want to roll back [1. # Achieved] from the previous 2 months, then try the following formula. For example when [2. Answer Date] is "2021-7-29", then calculate [1. # Achieved] from "2021-5-1" to "2021-5-29".

Rollback two months = 
var _start = EDATE ( MAX ( 'Table'[QuestionEndDate] ), -4 )
var PreviousPeriod = EDATE ( MAX ( 'Table'[2. Answer Date] ), -2 )
return 
    CALCULATE (
        SUM ( 'Table'[1. # Achieved] ), 
        filter ( 
            ALL ( 'Table' ),
            'Table'[QuestionEndDate] > _start
                && 'Table'[2. Answer Date].[Date] <= PreviousPeriod
        )    
    )

If the problem is still not resolved, please provide detailed error information or the expected result you expect. Let me know immediately, looking forward to your reply.

Best Regards,
Winniz

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