(1) Adding Fiscal Week to Calendar table; (2) Modifying Calendar Start Month

[Anonymous]

Hello.

1) I got this Dax to create a Calendar table:

Calendar = ADDCOLUMNS(CALENDAR(DATE(2022,1,31),DATE(2024,12,31)),"Year",YEAR([Date]))

Question: How do I add "Week" to the Calendar?

2) Normal calendar starts Jan 1 and ends Dec 31. 
Question: How can I modify the Calendar start and end dates, so that 1st month (Calendar start) is Jul 1, and 12th month (Calendar end) is Jun 30?

Thank you!

[v-jiewu-msft · Accepted solution]

Hi @Anonymous ,

If I understand correctly, the issue is that you want to add week to the calendar and modify the start dates. Please try the following methods and check if they can solve your problem:

1.Add a new column to the table.

Calendar = 
ADDCOLUMNS(
    CALENDAR(DATE(2022,1,31), DATE(2024,12,31)),
    "Year", YEAR([Date]),
    "Week", WEEKNUM([Date], 2)
)

2.Modify the start month.

Calendar = 
ADDCOLUMNS(
    CALENDAR(DATE(YEAR(TODAY()) - 1, 7, 1), DATE(YEAR(TODAY()), 6, 30)),
    "Year", YEAR([Date]),
    "Week", WEEKNUM([Date], 2)
)

Best Regards,

Wisdom Wu

[lbendlin]

1. WEEKNUM

2. You would do that manually as you already showed.

General advice:  Any custom calendar (especially with fiscal weeks) is better created and maintained outside of Power BI, for example as an Excel file on a SharePoint.