Forum Discussion

Czempijan's avatar
Czempijan
Icon for Helper II rankHelper II
6 years ago
Solved

Last Year Quarter on Custom Fiscal Calendar

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

Need Help
  • v-juanli-msft's avatar
    v-juanli-msft
    6 years ago

    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.

1 Reply

  • v-juanli-msft's avatar
    v-juanli-msft
    Icon for Community Support rankCommunity Support
    6 years ago

    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.