Forum Discussion

mcornfield's avatar
mcornfield
Helper III
4 years ago

DAX Calculation for Prior Period Date Range

Period Column is broken up into 2 month increments. I need to compare values vs the previous period and am not sure the DAX Formula. Below is what I have in PBI and then what I am looking to create. ...
Need Help
Show and Tell
Tips and Tricks
v-kkf-msft's avatar
v-kkf-msft
Community Support
4 years ago

Hi mcornfield ,

 

Please try the following formula:

 

Measure = 
var pre_date = 
    FILTER(
        ALL('Table'),
        'Table'[QuestionEndDate] < max('Table'[QuestionEndDate])
    )
var lastPeriod = MAXX( pre_date, 'Table'[QuestionEndDate] )
return
    CALCULATE(
        SUM('Table'[1. # Achieved]),
        filter(
            all('Table'),
            'Table'[QuestionEndDate] = lastPeriod 
        )
    )

 

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.

mcornfield's avatar
mcornfield
Helper III
4 years ago

It works in the one view but when i try to expand by other dimensions it just repeats the total.

Please see image for reference. In my live file I have other dimensions I have to bring in.

 

 

  • v-kkf-msft's avatar
    v-kkf-msft
    Community Support
    4 years ago

    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.