Forum Discussion
Helper IVCustom 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).
- 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?!
- 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
- amitchandak
Super User
refer for the calendar - .Creating Financial Calendar - From Any Month
https://community.powerbi.com/t5/Community-Blog/Creating-Financial-Calendar-Decoding-Date-and-Calendar-1-5-Power/ba-p/1187441
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-Power-BI-Turning/ba-p/1187482FY 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
Gingerjeans88Hi 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?
