Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

The ultimate Microsoft Fabric, Power BI, Azure AI & SQL learning event! Join us in Las Vegas from March 26-28, 2024. Use code MSCUST for a $100 discount. Register Now

Reply
mwaters4
Helper I
Helper I

Best Practice for using Calendar Table(s) across multiple reports

I am trying to understand the best way to create and use a calendar table(s) that can then be used across multiple reports, etc. that are created by many users.

 

Say I have a controller, a sales administrator, and an executive and they are all tasked with created some report in Power BI.  All of their reports need to start out with a Calendar table that we have created.

 

How best do I provide a starting template with that already built that they then can build the rest of the reports out from?  

Sort of similar question is the whole Shared and Certified Dataset thing.... I was thinking that there would be a way for me to create this Calendar table and then Share and Certify it in some repository that then other report creator users could connect to or start from.

 

Am I thinking about this incorrectly?  Can someone point me in the right direction please? 

Am I going to need to create this Calendar Table(s) somewhere outside of Power BI infrastructure like SQL Server, Analysis Services, etc.?

 

Thanks

Michael

1 ACCEPTED SOLUTION
TeigeGao
Solution Sage
Solution Sage

Hi @mwaters4 ,

Generally, we will create a calendar table in a report to make it easier to analysis the data based on continous date, we can create many different hierarchy, for example, the month, week, year, weekday, weeknum, etc. We will need to create calendar table based on the requirement, as a result, I will create different calendar table for different report.

Actually, it is easy to create a calendar table in PowerBI desktop, generally, I will use the following DAX query to create one, then edit some parameter to change it:

Calendar =
ADDCOLUMNS (
    CALENDAR ( DATE ( 2019, 1, 1 ), DATE ( 2019, 12, 31 ) ),
    "Year", YEAR ( [Date] ),
    "Month", MONTH ( [Date] ),
    "Day", DAY ( [Date] ),
    "WeekDay", WEEKDAY ( [Date] ),
    "WeekNum", WEEKNUM ( [Date] )
)

Best Regards,

Teige

View solution in original post

4 REPLIES 4
TeigeGao
Solution Sage
Solution Sage

Hi @mwaters4 ,

Generally, we will create a calendar table in a report to make it easier to analysis the data based on continous date, we can create many different hierarchy, for example, the month, week, year, weekday, weeknum, etc. We will need to create calendar table based on the requirement, as a result, I will create different calendar table for different report.

Actually, it is easy to create a calendar table in PowerBI desktop, generally, I will use the following DAX query to create one, then edit some parameter to change it:

Calendar =
ADDCOLUMNS (
    CALENDAR ( DATE ( 2019, 1, 1 ), DATE ( 2019, 12, 31 ) ),
    "Year", YEAR ( [Date] ),
    "Month", MONTH ( [Date] ),
    "Day", DAY ( [Date] ),
    "WeekDay", WEEKDAY ( [Date] ),
    "WeekNum", WEEKNUM ( [Date] )
)

Best Regards,

Teige

thanks for the response... so sounds like your answer is implying you create new calendar tables for every report you create... is that correct?

GilbertQ
Super User
Super User

Hi there

What you could do is to create the Calendar table from a dataflow. This will allow you to have one Calendar table available to all users.

You could then create a Power BI Template File, and the only table that it connects to is the dataflow Calendar Table.

Then when new users start creating reports, they will get the Calendar table by default?




Did I answer your question? Mark my post as a solution!

Proud to be a Super User!







Power BI Blog

this sounds potentially helpful, but Im not familiar with the "dataflows" in power bi... how does that work? 

can you point me in the right direction there please? 

 

thanks

 

Helpful resources

Announcements
Fabric Community Conference

Microsoft Fabric Community Conference

Join us at our first-ever Microsoft Fabric Community Conference, March 26-28, 2024 in Las Vegas with 100+ sessions by community experts and Microsoft engineering.

Top Solution Authors
Top Kudoed Authors