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

Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It 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

RTI Forums Carousel3

New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.


Fabric certifications survey

Certification feedback opportunity for the community.