Forum Discussion

Diksha

Regular Visitor

1 year ago

Solved

undefined

I am using below code for Date.

Date_Master =

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

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

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

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

var _startOfFiscalYear=1 // 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],2)),

"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],2),

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

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

"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)

)

Issue is this code starts from 1 Jan 2025. I want to use corporate colender which starts from 30 Nov,2024(Monday). So, how I will edit this so i can have corporate calender dates.

Need Help

v-ssriganesh
1 year ago
Hello Diksha,
Thank you for reaching out to the Microsoft Fabric Community.

I have reproduced your scenario in Power BI Desktop using your custom Date table DAX script and was able to achieve the expected output as per your requirement that is, having the calendar start from Monday, 30-Dec-2024 instead of 1-Jan-2025.

The root cause was that the CALENDAR() function originally started from DATE(_fromYear,1,1) which excludes dates before 1-Jan. To address this, I modified the start date logic as follows:

var _calendarStart = DATE(_fromYear,1,1) - WEEKDAY(DATE(_fromYear,1,1), 2) + 1

This shifts the calendar to begin on the Monday of the week containing 1-Jan, which in this case is 30-Dec-2024.

For your reference, I’m attaching the .pbix file that includes this working solution along with a table visual showing the calendar structure.

Thank you, Demert for sharing valuable insights.

If this information is helpful, please “Accept as solution” and give a "kudos" to assist other community members in resolving similar issues more efficiently.
Thank you.
Sample6666.pbix194 KB
v-ssriganesh
1 year ago
Hi Diksha,
Thank you for your follow-up.

You're right while the weeks now align with your corporate calendar, the default quarter logic in the date table still follows the calendar year format.

To meet your requirement, add a custom Corporate Quarter column to the Date_Master table using your quarter definitions.

Here's the Example DAX:

Corporate Quarter = SWITCH( TRUE(), [Date] >= DATE(2024,12,30) && [Date] <= DATE(2025,3,28), "Q1", [Date] >= DATE(2025,3,31) && [Date] <= DATE(2025,6,27), "Q2", [Date] >= DATE(2025,6,30) && [Date] <= DATE(2025,9,26), "Q3", [Date] >= DATE(2025,9,29) && [Date] <= DATE(2025,12,26), "Q4", "Out of Range" )

This checks that your visuals reflect corporate fiscal quarters, not calendar quarters. You can now use the Corporate Quarter field instead of the default Quarter or Year Quarter Name columns.

I trust this information proves useful. If it does, kindly “Accept as solution” and give it a "kudos" to help others locate it easily.
Thank you.

10 Replies

Demert

Resolver III

1 year ago

Hi Diksha ,

You have line in your code to change the start of the fiscal year :

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

I'm wondering is 30 Nov 2024 your closing date of the fiscal year and 2 dec 2024 your start of the fiscal year. If so you need to change the value to 12.

If that's not the case I suggest you remove the

"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

and create your own logic by creating the following calculated columns inside your date table.

Start_FinancialYear = STARTOFYEAR('Date_Master'[Date],"11-29")

End_FiscalYear = ENDOFYEAR('Date_Master'[Date],"11-29")

FiscalYear = year('Date_Master'[End_FiscalYear])

Diksha
Regular Visitor
1 year ago
My Apologies, Not 30 nov. it is 30 dec, 2024(Monday)
- Diksha
  Regular Visitor
  1 year ago
  As they are considering Monday as the first day of the year so, 30 Dec, 2024 is the first day of the year and so on and so forth.
  - Demert
    Resolver III
    1 year ago
    Hi Diksha my logic still stays the same for this problem you can create new calculated column based on the start of the fiscal year. I created the following start and end dates for 2025:
    with the following code:
    Start_FinancialYear = STARTOFYEAR(Date_Master[Date]) -WEEKDAY(STARTOFYEAR(Date_Master[Date]),2) + 1 End_FiscalYear = ENDOFYEAR(Date_Master[Date]) -WEEKDAY(ENDOFYEAR(Date_Master[Date]),2) FiscalYear = year('Date_Master'[End_FiscalYear])
v-ssriganesh
Community Support
1 year ago
Hello Diksha,
Thank you for reaching out to the Microsoft Fabric Community.

I have reproduced your scenario in Power BI Desktop using your custom Date table DAX script and was able to achieve the expected output as per your requirement that is, having the calendar start from Monday, 30-Dec-2024 instead of 1-Jan-2025.

The root cause was that the CALENDAR() function originally started from DATE(_fromYear,1,1) which excludes dates before 1-Jan. To address this, I modified the start date logic as follows:

var _calendarStart = DATE(_fromYear,1,1) - WEEKDAY(DATE(_fromYear,1,1), 2) + 1

This shifts the calendar to begin on the Monday of the week containing 1-Jan, which in this case is 30-Dec-2024.

For your reference, I’m attaching the .pbix file that includes this working solution along with a table visual showing the calendar structure.

Thank you, Demert for sharing valuable insights.

If this information is helpful, please “Accept as solution” and give a "kudos" to assist other community members in resolving similar issues more efficiently.
Thank you.
Sample6666.pbix194 KB
- Diksha
  Regular Visitor
  1 year ago
  Thanks. Its works well with weeks. But when I am taking quarter. For Q1 I want it to start from again 30 Dec, 2024 to 28th March, 2025. But is it taking same 1 Jan to 31 March. Any suggestions on this.
  - v-ssriganesh
    Community Support
    1 year ago
    Hi Diksha,
    Thank you for your follow-up.
    
    You're right while the weeks now align with your corporate calendar, the default quarter logic in the date table still follows the calendar year format.
    
    To meet your requirement, add a custom Corporate Quarter column to the Date_Master table using your quarter definitions.
    
    Here's the Example DAX:
    
    Corporate Quarter = SWITCH( TRUE(), [Date] >= DATE(2024,12,30) && [Date] <= DATE(2025,3,28), "Q1", [Date] >= DATE(2025,3,31) && [Date] <= DATE(2025,6,27), "Q2", [Date] >= DATE(2025,6,30) && [Date] <= DATE(2025,9,26), "Q3", [Date] >= DATE(2025,9,29) && [Date] <= DATE(2025,12,26), "Q4", "Out of Range" )
    
    This checks that your visuals reflect corporate fiscal quarters, not calendar quarters. You can now use the Corporate Quarter field instead of the default Quarter or Year Quarter Name columns.
    
    I trust this information proves useful. If it does, kindly “Accept as solution” and give it a "kudos" to help others locate it easily.
    Thank you.
v-ssriganesh
Community Support
1 year ago
Hello Diksha,
Just checking in have you been able to resolve this issue? If so, it would be greatly appreciated if you could mark the most helpful reply accordingly. This helps other community members quickly find relevant solutions.
Please don’t forget to “Accept as Solution” and Give “Kudos” if the response was helpful.
Thank you.
v-ssriganesh
Community Support
1 year ago
Hello Diksha,

Could you please confirm if your query has been resolved by the provided solution? If so, please mark it as the solution. This will help other community members solve similar problems faster.

Thank you.