Best Approach for Global Calendar

[Dilantha]

Hi,

I have created dashboard and their is request to change the format of calender.
I have used excel file with dates from 2020-01-01 to 2022-12-31 in single colum. And merged with other columns and added to global slicer.
After the review session their is need to get it without external file(excel).

1. I am thinking about implement stored procedure, If I add stored procedure how to add update it daily, then I can add new row to the calender table ?

2. Is their any best solution for that ?

Thank You

[amitchandak · Accepted solution]

@Dilantha , Not very clear. Why don't you create a calendar in power bi?

1.Creating Financial Calendar - From Any Month
https://community.powerbi.com/t5/Community-Blog/Creating-Financial-Calendar-Decoding-Date-and-Calend...
2.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-Powe...

or refer

To get the best of the time intelligence function. Make sure you have a date calendar and it has been marked as the date in model view. Also, join it with the date column of your fact/s. Refer :radacad sqlbi My Video Series Appreciate your Kudos.

[mahoneypat · Accepted solution]

This link/video may be useful.

Creating a simple date table in DAX - SQLBI

Regards,

Pat

[Dilantha]

Thank You for the response, It worked nicely

[amitchandak · Accepted solution]

@Dilantha , Not very clear. Why don't you create a calendar in power bi?

1.Creating Financial Calendar - From Any Month
https://community.powerbi.com/t5/Community-Blog/Creating-Financial-Calendar-Decoding-Date-and-Calend...
2.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-Powe...

or refer

To get the best of the time intelligence function. Make sure you have a date calendar and it has been marked as the date in model view. Also, join it with the date column of your fact/s. Refer :radacad sqlbi My Video Series Appreciate your Kudos.

[Dilantha]

Thank You for the response.
I considered your 3rd option and used CalenderAuto() function.

I have few questions from the aboue DAX function.

1. Currently I have below issue with date time column. When I used CALENDARAUTO() function dates start from 1899-01-01.
https://community.powerbi.com/t5/Translated-Spanish-Desktop/1899-12-31-in-time-column-and-0-00-in-da...

Still didnt resolved this one, but it can ignore from myend. The issue is my calender start from 189-01-01. Can we add start date as 2020-01-01 manually? It would be really helpful.

2. Does calendars most recent date will change dynamically ? I mean if I add CalenderAuto() today it shows data range as 1899-01-01 to 2020-12-31, If one record added today (2021-01-04) does calendar update to 1899-01-01 to 2021-01-04 ?
please suggest what changes should apply for the DAX function?

Thank You