Switching over to a new Fiscal Year

[Anonymous]

I have inherited a Power BI document that I didn't program and it's my first time using Power BI.

Each month, I added +1 to the months and it worked fine in August and September. Our new Fiscal Yera started October 1 and now I am trying to re-set the months and it's not working.

Can you please help me think through it?

Here is the last update from the end of the fiscal year:

For October, we want the YTD numbers that are highlighted in yellow to be reset so ONLY October is displayed and we want to add each month for FY 22/23 to it.

In the sales calls per month, we want to display ideally, October 2021 to October 2022 to have comparative data. So there should be an OCTOBER showing up AFTER the September number but the YTD totals should only reflect the FY 22/23 numbers.

The filter is set to:

And should pick up October 2022 numbers as I entered them:

However, it doesn’t display October and it doesn’t reset the YTD numbers

Here is the script from the date field:

Date =

//************** Script developed by RADACAD - edition: July 2021

//************** set the variables below for your custom date table setting

var _fromYear=2020 // set the start year of the date dimension. dates start from 1st of January of this year

var _toYear=2026   // set the end year of the date dimension. dates end at 31st of December of this year

var _startOfFiscalYear=10 // set the month number that is start of the financial year. example; if fiscal year start is July, value is 7

//**************

var _today=TODAY()

return

ADDCOLUMNS(

    CALENDAR(

                DATE(_fromYear,1,1),

                DATE(_toYear,12,31)

),

"Year",YEAR([Date]),

"Start of Year",DATE( YEAR([Date]),1,1),

"End of Year",DATE( YEAR([Date]),12,31),

"Month",MONTH([Date]),

"Start of Month",DATE( YEAR([Date]), MONTH([Date]), 1),

"End of Month",EOMONTH([Date],0),

"Days in Month",DATEDIFF(DATE( YEAR([Date]), MONTH([Date]), 1),EOMONTH([Date],0),DAY)+1,

"Year Month Number",INT(FORMAT([Date],"YYYYMM")),

"Year Month Name",FORMAT([Date],"YYYY-MMM"),

"Day",DAY([Date]),

"Day Name",FORMAT([Date],"DDDD"),

"Day Name Short",FORMAT([Date],"DDD"),

"Day of Week",WEEKDAY([Date]),

"Day of Year",DATEDIFF(DATE( YEAR([Date]), 1, 1),[Date],DAY)+1,

"Month Name",FORMAT([Date],"MMMM"),

"Month Name Short",FORMAT([Date],"MMM"),

"Quarter",QUARTER([Date]),

"Quarter Name","Q"&FORMAT([Date],"Q"),

"Year Quarter Number",INT(FORMAT([Date],"YYYYQ")),

"Year Quarter Name",FORMAT([Date],"YYYY")&" Q"&FORMAT([Date],"Q"),

"Start of Quarter",DATE( YEAR([Date]), (QUARTER([Date])*3)-2, 1),

"End of Quarter",EOMONTH(DATE( YEAR([Date]), QUARTER([Date])*3, 1),0),

"Week of Year",WEEKNUM([Date]),

"Start of Week", [Date]-WEEKDAY([Date])+1,

"End of Week",[Date]+7-WEEKDAY([Date]),

"Fiscal Year",if(_startOfFiscalYear=1,YEAR([Date]),YEAR([Date])+ QUOTIENT(MONTH([Date])+ (13-_startOfFiscalYear),13)),

"Fiscal Quarter",QUARTER( DATE( YEAR([Date]),MOD( MONTH([Date])+ (13-_startOfFiscalYear) -1 ,12) +1,1) ),

"Fiscal Month",MOD( MONTH([Date])+ (13-_startOfFiscalYear) -1 ,12) +1,

"Day Offset",DATEDIFF(_today,[Date],DAY),

"Month Offset",DATEDIFF(_today,[Date],MONTH),

"Quarter Offset",DATEDIFF(_today,[Date],QUARTER),

"Year Offset",DATEDIFF(_today,[Date],YEAR)

)

Here is another screen shot.

If I change the date back to 1, everything is blank:

If I change it to 13 it adds October at the beginning but it’s not October 2022 numbers, as October 2022 is not 300.

I can attach the trace report , so that must be October 2021 number = 300 but that doesn’t check out either because there are 718 entries for October 2021.

Let me know what other screenshot or files I can send.

Thank you, Sabine

[v-henryk-mstf]

Hi @Anonymous ,

Whether the advice given by @amitchandak  has solved your confusion, if the problem has been solved you can mark the reply for the standard answer to help the other members find it more quickly. If not, please point it out.

Looking forward to your feedback.

Best Regards,
Henry

[amitchandak · Accepted solution]

@Anonymous , Based on what I got YTD from Oct sep you can get like

YTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESYTD('Date'[Date],"09/30"))
Last YTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESYTD(dateadd('Date'[Date],-1,Year),"09/30"))

Time Intelligence, DATESMTD, DATESQTD, DATESYTD, Week On Week, Week Till Date, Custom Period on Period,
Custom Period till date: https://youtu.be/aU2aKbnHuWs&t=145s

DAX Calendar - Standard Calendar, Non-Standard Calendar, 4-4-4 Calendar
https://www.youtube.com/watch?v=IsfCMzjKTQ0&t=145s