Last Year Quarter on Custom Fiscal Calendar

[Czempijan]

Hi,

I tried to solve this, but couldn't find suitable solutions. Here is my problem:

I am running on a custom calendar i.e. fiscal year starts November, thus my calendar which is set up as a 'Data Table', looks like this:

My Values table looks as below, this is full Profit&Loss data, but let's focus on Net Revenue (calculated as below)

Values=SUM(Master_Pull[Value])/1000

Net Revenue=CALCULATE([Values],FILTER(Master_Pull,Master_Pull[Functional Area]="Net Revenues"))

Now, what I am trying to do is to calculate data for last year quarter, so I am able to compare Q1'20 to Q1'19  - how this can be achieved?

Thank you so much in advance for your inputs,

Czempijan

[v-juanli-msft · Accepted solution]

Hi @Czempijan 

Create calendar table:

calendar = CALENDARAUTO()

add columns
Fiscal year = IF(MONTH([Date])>=11,YEAR([Date])+1,YEAR([Date]))

Fiscal month = IF(MONTH([Date])<11,MONTH([Date])+2,MONTH([Date])-10)

Fiscal quarter =
SWITCH (
    TRUE (),
    [Fiscal month] >= 1
        && [Fiscal month] <= 3, 1,
    [Fiscal month] >= 4
        && [Fiscal month] <= 6, 2,
    [Fiscal month] >= 7
        && [Fiscal month] <= 9, 3,
    [Fiscal month] >= 10
        && [Fiscal month] <= 12, 4
)

Create measures

current = CALCULATE(SUM(Sheet8[value])/1000,FILTER(Sheet8,Sheet8[Functional Area]="Net Revenues"))

Method 1:

Create measures

last year's quarter1 = CALCULATE([current],SAMEPERIODLASTYEAR('calendar'[Date]))

Method 2:

Create measures

Measure =
CALCULATE (
    [current],
    FILTER (
        ALLSELECTED ( 'calendar' ),
        'calendar'[Fiscal year]
            = MAX ( 'calendar'[Fiscal year] ) - 1
            && 'calendar'[Fiscal quarter] = MAX ( 'calendar'[Fiscal quarter] )
    )
)

Measure 2 =
IF (
    ISINSCOPE ( 'calendar'[Fiscal quarter] ),
    [Measure],
    SUMX ( ALL ( 'calendar'[Fiscal quarter] ), [Measure] )
)


last year's quarter2 =
IF (
    [current] <> BLANK (),
    IF (
        ISINSCOPE ( 'calendar'[Fiscal year] ),
        [Measure 2],
        SUMX ( ALL ( 'calendar'[Fiscal year] ), [Measure 2] )
    )
)

Best Regards
Maggie
Community Support Team _ Maggie Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.