Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

To celebrate FabCon Vienna, we are offering 50% off select exams. Ends October 3rd. Request your discount now.

Reply
dbarseg1
Frequent Visitor

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

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:

dbarseg1_0-1720657214659.png

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. 

dbarseg1_1-1720657475936.png

 

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. 
dbarseg1_3-1720657648653.png
Thank you so much for your consideration and help!

 

 

1 ACCEPTED SOLUTION
rajendraongole1
Super User
Super User

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





Did I answer your question? Mark my post as a solution!

Proud to be a Super User!





View solution in original post

3 REPLIES 3
LukeHRP
New Member

I use flags in my date table for current year, current month, current week etc where the current period for each is set to 0 and any prior ones are -1, -2 etc and subsequent ones are 1, 2 etc. This allows me to simplify the DAX by having a Calculation Group called Time Intelligence with standards functions for specific time buckets (YTD, PYTD etc). So to create a calc item for YTD for example, you can just have:

 

YTD = CALCULATE (
    SELECTEDMEASURE(),
    'Fiscal Calendar'[Current Year] = 0
)
 
And then apply this to each of the base measures in your model to get the YTD value for each. PY would be the same but filtered to year -1. PYTD is a bit more complicated as you need to account for the prior year but only up to the equivalent date in that year but still all fairly manageable with this set up. Main thing is setting up your date table correctly as everything then flows off that.
 
Regards the built in time intelligence functions, they only work with a standard gregorian calendar as far as I'm aware (Could've changed now) so if you're using a custom fiscal calendar, you'll need to recreate these functions to fit your structure accordingly.
rajendraongole1
Super User
Super User

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





Did I answer your question? Mark my post as a solution!

Proud to be a Super User!





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

Helpful resources

Announcements
September Power BI Update Carousel

Power BI Monthly Update - September 2025

Check out the September 2025 Power BI update to learn about new features.

August 2025 community update carousel

Fabric Community Update - August 2025

Find out what's new and trending in the Fabric community.

Top Solution Authors