Skip to main content
cancel
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.

Reply
Gingerjeans88
Helper IV
Helper IV

Custom Fiscal Year and YoY by Fiscal Week - Help!

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. 

3 REPLIES 3
amitchandak
Super User
Super User

@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

 

 

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

Announcements
RTI Forums Carousel3

New forum boards available in Real-Time Intelligence.

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

LearnSurvey

Fabric certifications survey

Certification feedback opportunity for the community.