Trailing 12 months rolling 4 weeks DAX Help Required

DAX DAX Calculations
1

Hi,

This is my first post and first ever PBI Model, so please go easy on me :grin:

I have created a report that in the top visual, shows sales orders from the current week ending -365 days to the current week.

1st Report
1st Report1442×727 77.9 KB

Here is the measure:

Orders TTM = 
CALCULATE (
    SUM ( emea_pbi_salesorders[Nett] ),
   FILTER(
        'Date',
        'Date'[Date] >= TODAY() -365 &&
        'Date'[Date] <= MAX('Date'[Date])
    )
)

The report was received well, but I have been asked to modify it to show the the sum of the rolling 4 week value.

I created the following measure to accomplish this.

Sales Rolling 4 Weeks = 
VAR Weeks =
    DATESBETWEEN (
        'Date'[Week Ending],
        MAX ( 'Date'[Week Ending] ) -21,
        MAX ( 'Date'[Week Ending] )
    )
VAR Result =
    CALCULATE ( [Total Sales], Weeks)
RETURN
    Result

Total Sales is just

Total Sales = SUM(emea_pbi_salesorders[nettprice])

pbi2
pbi21362×383 20.1 KB

The issue I have, is trying to restrict the values to the previous 12 months.
I managed to fix it using an additional date table, but this was causing other issues, and I really dont think this was the way to proceed?

I also tried combining the two measures, but this caused the rolling 4 weeks to stop working.

Orders TTM2 = 
CALCULATE (
    [Sales Rolling 4 Weeks],
   FILTER(
        'Date',
        'Date'[Date] >= TODAY() -365 &&
        'Date'[Date] <= MAX('Date'[Date])
    )
)

pbi3

Could anyone point me in the right direction please?
I am very new (~ 6 weeks!) to Power BI and DAX, and don’t really have the vocabulary to ask Google.

Obfuscated model attached…

TTM Test.pbix (180.5 KB)

Thanks in Advance,

Kind Regards,

Mark

2

Before I dig too deep into this, I was able to get a simple filter to zero in, does this get you what you need?

image
image305×747 14.4 KB

image

3

Thanks for the reply.

Ideally, I’d like to do it in DAX if possible?
I don’t want any slicers, or visual filters on this report.

Kind Regards,

Mark

4

Hello @mark.roberts

You can try something like this

Sales Rolling 4 Weeks =
VAR _date12MonthAgo =
    TODAY () - 365
VAR _upperBoundDate =
    MAX ( 'Date'[Week Ending] )
VAR _date4WeeksAgo = _upperBoundDate - 21
VAR _lowerBoundDate =
    IF ( _date4WeeksAgo >= _date12MonthAgo, _date4WeeksAgo, _date12MonthAgo )
VAR Weeks =
    DATESBETWEEN ( 'Date'[Week Ending], _lowerBoundDate, _upperBoundDate )
VAR Result =
    CALCULATE ( [Total Sales],  Weeks )
RETURN
    Result

I reformatted it slightly as I like have everything in variables as it makes it easier to debug if needed.
First thing I did is the same as you, so calculated ending date. Then I needed the starting date knowing that it needs you be after 12 months ago.

So I calculated it by using If statement, and if my calculated date 4 weeks ago was before today -365, I just replaced it.

Hopes that helps

2 Likes
5

Many Thanks @piniusz
This works perfectly!

Thank you for taking the time to help me out.
I think I understand the gist of what is happening, but will take some time in DAX Studio to fully understand the code.

Thanks Again,

Kind Regards,

Mark