Need Last Year 4 Week Aggregate

[pokdbz]

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
)
)

[Anonymous]

Maybe this would help you?

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

Best

Darek

[Anonymous]

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

[pokdbz]

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.

[Anonymous]

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

[Anonymous]

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