Prior Year 12 Month Rolling

DAX DAX Calculations
1

Hi All,

I am using the following for the 12 Month Rolling Sales Figure.

Sales12M = CALCULATE (
    [Sales],
    DATESBETWEEN (
        Calendar[Date],
        NEXTDAY ( SAMEPERIODLASTYEAR ( LASTDATE ( Calendar[Date] ) ) ),
        LASTDATE ( Calendar[Date] )
    )
)

What is the best practice for getting the 12 month Rolling for the previous Year.

Thanks

Chris

2

Hi Chris have you gone through this one?

Sam

3

Hi Sam

That worked fantastically well for the 12 Month Calculations that I have so thank you.

The other conundrum that I have is I have a number of Products that are linked to a profile and that Profile has an activation Date.

If that Rolling Period has 12 Months from Profile Activation Date the Measure is correct but if that period is only say 5 months ago how would I program the Measure in that situation where the rolling period is dynamic but not greater than than 12 months ago.

Hope this makes sense

Chris

4

Hi I have resolved the query above but it has thrown up another issues whereby you have to use a Contiguous date table to be able to use the SAMEPERIODLASTYEAR Function. I have battled with this all morning an have not got anywhere so hopefully I will get some help.

Test Quantity Rolling PY = 
SWITCH(
    TRUE(),
       [ProfileActivationDifference] < 12,  CALCULATE (
            [Active Quantity Rolling],
            DATESBETWEEN (
            Calendar[Date],
            **SAMEPERIODLASTYEAR(ProfileProduct[ProfileActivatedDate])**,
            SAMEPERIODLASTYEAR(LASTDATE ( Calendar[Date] )))),
        [ProfileActivationDifference] > 11,  CALCULATE (
            [Active Quantity Rolling],
        DATESBETWEEN (
        Calendar[Date],
        NEXTDAY(SAMEPERIODLASTYEAR( SAMEPERIODLASTYEAR ( LASTDATE ( Calendar[Date] ) ) )),
        SAMEPERIODLASTYEAR(LASTDATE ( Calendar[Date] )
    )
        
    )
))

The bold text is where I have the issue. ProfileProduct is a dimension Table that is related to my factTable. Each of my Products has an activated date and I want to use this to give the start of same period last year but from what I can figure out you cannot do this due to non contiguous dates.

This means I need to be able to use the Date Table for the SAMEPERIODLASTYEAR Function but I do not know how to pass the ProfileActivatedDate to the Date Table.

I get the error message a date column containing duplicates was specified in the call to to function LASTDATE: This is not supported.

I have also used VALUES(ProfileProduct[ProfileActivatedDate]) as this gives me a Single Unique Date in it’s own Measure but complains when used with SAMEPERIODLASTYEAR. What am I missing?

Model

ProfileProduct is joined to factSales by ProductID

image
image.png789×810 23.7 KB

Hope this makes sense and any help gratefully received.

Thanks

Chris

5

Yes that correct regarding the date table, but that is always the case. You should always have a detailed date table that has ever single date in it. That way you can use time intelligence functions very easily.

This to me look far to complicated for what you need.

image
image.png781×437 11.5 KB

I’m a bit confused as to what you’re after with this.

The get the rolling total of LY should be super easy. You just need to use exactly the same rolling total technique but instead of using say for example [Sales] you should use [Sales LY]. With the Sales LY measure just using simple time intelligence.

I think you might be over thinking it here.

See how you go with this.

Thanks
Sam