Join us at FabCon Atlanta from March 16 - 20, 2026, for the ultimate Fabric, Power BI, AI and SQL community-led event. Save $200 with code FABCOMM.
Register now!To celebrate FabCon Vienna, we are offering 50% off select exams. Ends October 3rd. Request your discount now.
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:
Solved! Go to 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!!
Proud to be a Super User! | |
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:
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!!
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: