Custom Fiscal Year and YoY by Fiscal Week - Help!

[Gingerjeans88]

Hi! 

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. 

[amitchandak]

@Gingerjeans88 ,

refer for the calendar - .Creating Financial Calendar - From Any Month
https://community.powerbi.com/t5/Community-Blog/Creating-Financial-Calendar-Decoding-Date-and-Calend...
 

Any Weekday Week - Start From Any day of Week
https://community.powerbi.com/t5/Community-Blog/Any-Weekday-Week-Decoding-Date-and-Calendar-2-5-Powe...

FY Week : https://www.youtube.com/watch?v=euIC0dgGTNM

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
https://medium.com/@amitchandak.1978/power-bi-ytd-questions-time-intelligence-1-5-e3174b39f38a
https://www.youtube.com/watch?v=km41KfM_0uA

[Gingerjeans88]

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?

[Gingerjeans88]

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