Showing results for 
Search instead for 
Did you mean: 

Fabric is Generally Available. Browse Fabric Presentations. Work towards your Fabric certification with the Cloud Skills Challenge.

Helper IV
Helper IV

Custom Fiscal Year and YoY by Fiscal Week - Help!



A couple of things I'd be so grateful for help with

I have a report based on Dynamics CRM data for a school - I need to be able to show number of Applications made by fiscal year  - the year starts on the Monday of the second week of September (so not a fixed date). 


  1. I need to be able to see Year on Year applications by week (so how many in week 1 of this year vs week 1 of last year), always just up to the current point. So for the 'last year' value, it should be up to 'today last year' if possible. Is it better to create a date dimension table with a custom fiscal start and end date (not sure how I do it without a fixed date), and also the custom fiscal week column, or can I just add a 'Fiscal Week' column to my Applications fact table?
    • Either way - could I have help with the code for the table / column?! 
  2. I also need to be able to see YTD and Previous YTD number of applications, again up until 'today' either this year or last. 

I have tried a few things and am stumped! 


Thanks so much. 

Super User
Super User

@Gingerjeans88 ,

refer for the calendar - .Creating Financial Calendar - From Any Month

Any Weekday Week - Start From Any day of Week


FY Week :


YTD with FY Week

Year Week = [Year]*100 +[Week]
YTD= CALCULATE(sum('order'[Qty]), FILTER(ALL('Date'),'Date'[Year]=max('Date'[Year]) && 'Date'[Week] <= Max('Date'[Week]) ))
LYTD = CALCULATE(sum('order'[Qty]), FILTER(ALL('Date'),'Date'[Year]=max('Date'[Year])-1 && 'Date'[Week] <= Max('Date'[Week])))



YTD with Date

YTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESYTD('Date'[Date],"12/31"))
Last YTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESYTD(dateadd('Date'[Date],-1,Year),"12/31"))
This year Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESYTD(ENDOFYEAR('Date'[Date]),"12/31"))
Last year Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESYTD(ENDOFYEAR(dateadd('Date'[Date],-1,Year)),"12/31"))
Last to last YTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESYTD(dateadd('Date'[Date],-2,Year),"12/31"))


Power BI — Year on Year with or Without Time Intelligence



Hi Amit,


Thanks so much for your reply - however, your calculations for the custom date table are not quite what I need. They are really useful if the start of my financial year was the first day of a month, but it's more variable than that. The first day of the financial (academic year) is the beginning (monday) of the third week in September. How would I account for this as the actual date will be different every year?

Any update @amitchandak ? Or does anybody else have any helpful tips?

Helpful resources

PBI November 2023 Update Carousel

Power BI Monthly Update - November 2023

Check out the November 2023 Power BI update to learn about new features.

Community News

Fabric Community News unified experience

Read the latest Fabric Community announcements, including updates on Power BI, Synapse, Data Factory and Data Activator.

Power BI Fabric Summit Carousel

The largest Power BI and Fabric virtual conference

130+ sessions, 130+ speakers, Product managers, MVPs, and experts. All about Power BI and Fabric. Attend online or watch the recordings.

Top Solution Authors
Top Kudoed Authors