cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
mwaters4 Regular Visitor
Regular Visitor

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

Accepted Solutions
Community Support Team
Community Support Team

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

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

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

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 Datanaut!"
Power BI Blog
Community Support Team
Community Support Team

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

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

mwaters4 Regular Visitor
Regular Visitor

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

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

 

mwaters4 Regular Visitor
Regular Visitor

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

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

Helpful resources

Announcements
New Topics Started Badges Coming

New Topics Started Badges Coming

We're releasing new versions of the badge that everyone's talking about. ;) Check your inbox for notifications.

MBAS 2020

Save the new date (and location)!

Our business applications community is growing—so we needed a different venue, resulting in a new date and location. See you there!

Difinity Conference

Difinity Conference

The largest Power BI, Power Platform, and Data conference in New Zealand

Top Solution Authors
Top Kudoed Authors (Last 30 Days)