topic Need Last Year 4 Week Aggregate in DAX Commands and Tips

Need Last Year 4 Week Aggregate

pokdbz — Tue, 09 Jul 2019 15:30:52 GMT

How can I create a calculation that looks back one year and then does a 4 week aggregate?

We do not use a standard calendar so some of the DAX functions do not work becauses of this.

Currently this is what I use for a 4 week/28 day aggregate. How can I add the Last Year look back to this?

CALCULATE (
[Sales],
DATESINPERIOD (
'Calendar - Fiscal'[Date],
LASTDATE ( 'Calendar - Fiscal'[Date] ),
-28,
DAY
)
)

Re: Need Last Year 4 Week Aggregate

Anonymous — Tue, 09 Jul 2019 16:09:15 GMT

What does it mean when you say "last year"? Are you talking about the usual year shift in the ordinary calendar or something like a custom year (say, fiscal)?

Best

Darek

Re: Need Last Year 4 Week Aggregate

Anonymous — Tue, 09 Jul 2019 16:13:06 GMT

For a regular calendar this should work:

[Measure] =
CALCULATE (
    [Sales],
    DATESINPERIOD (
        'Calendar - Fiscal'[Date],
        SAMEPERIODLASTYEAR( LASTDATE ( 'Calendar - Fiscal'[Date] ) ),
        -28,
        DAY
    )
)

But beware! If the dates go off the beginning of the calendar, you will not get a full 28-day period. You have to decide what you want to do in case the shift does not return 28 days.

I also hope that 'Calendar - Fiscal' is a PROPER DATE TABLE and is marked as such. Otherwise, this stuff will not work.

Best

Darek

Re: Need Last Year 4 Week Aggregate

Anonymous — Tue, 09 Jul 2019 16:26:28 GMT

Maybe this would help you?

https://www.youtube.com/watch?v=wHn968f1E58

Best

Darek

Re: Need Last Year 4 Week Aggregate

pokdbz — Tue, 09 Jul 2019 16:41:57 GMT

This is a proper date table, but its not a standard Calendar table. Its a Fiscal Calendar so alot of the functions will not work. We cannot hard coded dates either because the next year does not start and end on the same as the previous year.

Re: Need Last Year 4 Week Aggregate

Anonymous — Tue, 09 Jul 2019 16:55:45 GMT

To be able to move in time in a custom calendar you need to create columns that will enable this. For instance, all days should have attributes like: FiscalYearNumber, FiscalYearName, FiscalMonthNumberInYear, FiscalMonthConsecutiveNumberAcrossAllYears,....

Once you have them (please have a look at the YT vid link I've sent you before), then and only then can you build your calculations.

For calculations when using custom calendars please go to Period Comparison Patterns with Custom Calendars

Best

Darek