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
ayeoh
Employee
Employee

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

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!
1 ACCEPTED SOLUTION
v-jiewu-msft
Community Support
Community Support

Hi @ayeoh ,

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

View solution in original post

2 REPLIES 2
v-jiewu-msft
Community Support
Community Support

Hi @ayeoh ,

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
Super User
Super User

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.

Helpful resources

Announcements
LearnSurvey

Fabric certifications survey

Certification feedback opportunity for the community.

PBI_APRIL_CAROUSEL1

Power BI Monthly Update - April 2024

Check out the April 2024 Power BI update to learn about new features.

April Fabric Community Update

Fabric Community Update - April 2024

Find out what's new and trending in the Fabric Community.