How to calculate sameperiodlastyear in a 4-4-5

[Andresrsg]

Hi Everyone, 

I'm trying to set up a measure similar to SAMEPERIODLASTYEAR.  I can;t use this formula any longer because the calendar is now customised to 4-4-5.  my approach is to filter the calendar table by the "MonthYear"  column(using Last year month) , which would give me the correct range of dates , and then used the minimum and maximum dates to calculate my measure. HOwever I am struggling to get an answer.

Act LY =

VAR CurrentDatex = SELECTEDVALUE('Calendar'[Date])

VAR CurrentYear = YEAR(CurrentDatex)

VAR CurrentMonth = MONTH(DATEVALUE(SELECTEDVALUE('Calendar'[MonthYear])))

VAR LastYear = CurrentYear - 1

VAR PreviousYearStartDate =

    DATE(LastYear, CurrentMonth, 1)

VAR filteredmindate =

    CALCULATETABLE(

        'Calendar',

        FILTER(

            ALL('Calendar'),

            'Calendar'[MonthYear] = FORMAT(PreviousYearStartDate, "Mmm-YY")

        )

    )

Return

    CALCULATE(

        [Actuals],

        FILTER(

            ALL('Calendar'),

            'Calendar'[Date] >= MINX(filteredmindate, 'Calendar'[Date])

                && 'Calendar'[Date] <= MAXX(filteredmindate, 'Calendar'[Date])

        )

    )

Any help would be appreciated

[Andresrsg · Accepted solution]

Hi Again, 

Given the complexity of my calendar table, I have managed to get what I wanted with an actual easy solution

Act LY =

Var monthsselected = DISTINCT('Calendar'[MonthLY])

VAR filteredCal =

     FILTER(

        ALL('Calendar'),

        'Calendar'[MonthYear] IN monthsselected

    )

Return

    CALCULATE(

        [Actuals],

        FILTER(

            ALL('Calendar'),

            'Calendar'[Date] >= MINX(filteredCal, 'Calendar'[Date])

                && 'Calendar'[Date] <= MAXX(filteredCal, 'Calendar'[Date])

        )

    )
Thank you

[Anonymous]

Hi
Try the below Code,

Act LY =
VAR CurrentDateX = SELECTEDVALUE('Calendar'[Date])
VAR CurrentYear = YEAR(CurrentDateX)
VAR CurrentMonth = MONTH(CurrentDateX)

VAR LastYearStartDate =
    DATE(CurrentYear - 1, CurrentMonth, 1)

VAR LastYearMonthYear = FORMAT(LastYearStartDate, "Mmm-YY")

VAR FilteredMinDate =
    CALCULATETABLE(
        'Calendar',
        FILTER(
            ALL('Calendar'),
            'Calendar'[MonthYear] = LastYearMonthYear
        )
    )

RETURN
    CALCULATE(
        [Actuals],
        FILTER(
            ALL('Calendar'),
            'Calendar'[Date] >= MINX(FilteredMinDate, 'Calendar'[Date]) &&
            'Calendar'[Date] <= MAXX(FilteredMinDate, 'Calendar'[Date])
        )
    )

[Andresrsg]

Hi @anilkapkoti, 
Thank you for that I tried this before, but unfortunatelythe results are zero

I managed to get the result with this formula, however if drill up the matrix to FY and applie a filter to a Quarter or month it will offcourse show the year results, so it is not as dynamic

Act LY =

SWITCH(

    TRUE(),

    ISINSCOPE('Calendar'[MonthYear]),

    CALCULATE(

        [Actuals],

        FILTER(

            ALL('Calendar'),

            'Calendar'[CurMonthOffset] = MAX('Calendar'[CurMonthOffset]) - 12

        )

    ),

    ISINSCOPE('Calendar'[FiscalQuarter]),

    CALCULATE(

        [Actuals],

        FILTER(

            ALL('Calendar'),

            'Calendar'[CurQuarterOffset] = MAX('Calendar'[CurQuarterOffset]) - 4

        )

    ),

    ISINSCOPE('Calendar'[FiscalYear]),

    CALCULATE(

        [Actuals],

        FILTER(

            ALL('Calendar'),

            'Calendar'[CurFiscalYearOffset] = MAX('Calendar'[CurFiscalYearOffset]) - 1

        )

    )

)

Thank you though