DAX Calculation for Prior Period Date Range

[mcornfield]

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. After this I need to show the Variance. (period is based of a Date Column)

[v-kkf-msft]

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]

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]

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.

[amitchandak]

@mcornfield , if you have date range preferred from a joined date table

same period based on date range
Last Period =
var _max =maxx(date,date[date])
var _min =maxx(date,date[date])
var datediff1 = datediff(_min,_max,day)
var _maxX = _max-datediff1
var _minX = _min -datediff1
return
CALCULATE(SUM(Sales[Sales Amount]),filter(all(date),date[date]<=_maxX &&date[date]>=_minX))

or refer

How to use two Date/Period slicers :https://www.youtube.com/watch?v=WSeZr_-MiTg

[mcornfield]

Thanks, So Everything is in one table. the columns I have are Date, Year, Month Period, Period Number, and # Acheived.   SO I am not sure how to implement your suggestion. 

[amitchandak]

@mcornfield , prefer to have date table, "all" does not go fine when what do display row level data.

You can replace the date table with your table and check

To get the best of the time intelligence function. Make sure you have a date calendar and it has been marked as the date in model view. Also, join it with the date column of your fact/s. Refer :radacad sqlbi My Video Series Appreciate your Kudos.

[mcornfield]

Thank you, the numbers look way high. I am wondering if it's not adhering to the slicers I have.

Any thoughts would be appreciated 🙂

[Ashish_Mathur]

Hi,

Share the link from where i can download your PBI file.

[mcornfield]

https://drive.google.com/file/d/11Zs9wJZJxPEhZxGzwGYt-ZovilEyzXxZ/view?usp=sharing

[Ashish_Mathur]

Hi,

You should ideally have a seperate Calendar Table with the Year and period columns appearing in the Calendar Table.  We should then create a relationship between the Date column in your Table with the Date column in the Calendar Table.  However the confusion at this stage would be that which Date column in your Table should be used for creating the relationship - QuestionEndDate or 2. Answer Date?  Please clarify.

[mcornfield]

Period is based off of PeriodEndDate Thanks!

[Ashish_Mathur]

Hi,

That does not answer my question.  I am sure someone else will help you with this.

[mcornfield]

https://drive.google.com/file/d/11Zs9wJZJxPEhZxGzwGYt-ZovilEyzXxZ/view?usp=sharing