How to calculate Variance % for CY vs PY Revenue without using a continuous date column?

[dbarseg1]

Hi all, 
Slightly long question here so I appreciate your time!!

I am trying to create a variance % column to show the change in revenue from last year to this year in a table. In order to do so, I first need a measure that calculates previous year revenue using FISCAL dates, not actual dates. Our company uses the 445 Fiscal Calendar so the first and second month of each quarter have 4 weeks, and the third month of the quarter has 5 weeks. For this reason, I'm not sure how to create a continuous date column that corresponds to the fiscal date criteria needed. Instead, I tried for the next best thing to continous and created a column for Month-Year in my date table and sales table. To get the previous year revenue, I found online that i needed to use the sameperiodlastyear function, but that doesn't seem to work with my column that's formatted as Month-Year. For context, Here is what my date table looks like:

I've made sure to mark this table as my date table but I used the Date (Actual) column in the picture to create that distinction. As for my data relationships, below is a picture of how I connected my date table to my sales table using a many to many relationship via the Month-Year column shown above. 

The problem is that when I make my DAX expression for PY Revenue as so:

PY Revenue =

CALCULATE(

    [Total Revenue],

    SAMEPERIODLASTYEAR('Fiscal Calendar'[Date]))

and add the PY Revenue to my matrix table, it doesn't load. For reference, the rows in my matrix include a hierarchy of Fiscal Year, Fiscal Quarter, Fiscal Month, and Fiscal Week. I'm new to Power BI and don't yet understand how relationships or DAX work so any help is appreciated. 

Thank you so much for your consideration and help!

[rajendraongole1 · Accepted solution]

Hi @dbarseg1 -I hope in your date table fiscal year, fiscal month is exist,if not below is the code added and then create a custom calculation as like below to shift the dates according to your fiscal calendar. 

"FiscalYear", IF(MONTH([Date]) >= 4, YEAR([Date]), YEAR([Date])-1)

FiscalMonth",
IF (
MONTH([Date]) >= 4,
MONTH([Date]) - 3,
MONTH([Date]) + 9

PY revenue calculation as below:

PY Revenue =
CALCULATE(
[Total Revenue],
FILTER (
ALL ( 'DateTable' ),
'DateTable'[FiscalYear] = MAX ( 'DateTable'[FiscalYear] ) - 1 &&
'DateTable'[FiscalMonth] = MAX ( 'DateTable'[FiscalMonth] )
)
)

This calculation filters the date table to the same fiscal month of the previous FY

Did I answer your question? Mark my post as a solution! This will help others on the forum!
Appreciate your Kudos!!

[dbarseg1]

Thank you so much!!

I've been stuck on this for so long. I used your logic with Chat GPT to ensure that all my date hierarchy in the matrix show prior year. Here it is in case others want to reference an expanded version of your code:

PY Revenue =

SWITCH(

    TRUE(),

    ISINSCOPE('Fiscal Calendar'[Fiscal Month]),

    CALCULATE(

        [Total Revenue],

        FILTER(

            ALL('Fiscal Calendar'),

            'Fiscal Calendar'[Fiscal Year] = MAX('Fiscal Calendar'[Fiscal Year]) - 1 &&

            'Fiscal Calendar'[Fiscal Month] = MAX('Fiscal Calendar'[Fiscal Month])

        )

    ),

    ISINSCOPE('Fiscal Calendar'[Fiscal Quarter]),

    CALCULATE(

        [Total Revenue],

        FILTER(

            ALL('Fiscal Calendar'),

            'Fiscal Calendar'[Fiscal Year] = MAX('Fiscal Calendar'[Fiscal Year]) - 1 &&

            'Fiscal Calendar'[Fiscal Quarter] = MAX('Fiscal Calendar'[Fiscal Quarter])

        )

    ),

    ISINSCOPE('Fiscal Calendar'[Fiscal Year]),

    CALCULATE(

        [Total Revenue],

        FILTER(

            ALL('Fiscal Calendar'),

            'Fiscal Calendar'[Fiscal Year] = MAX('Fiscal Calendar'[Fiscal Year]) - 1

        )

    ),

    BLANK()

)