Forum Discussion

mwaters4's avatar
mwaters4
Helper I
7 years ago
Solved

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

Need Help
  • TeigeGao's avatar
    TeigeGao
    7 years ago

    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

4 Replies

  • GilbertQ's avatar
    GilbertQ
    Super User
    7 years ago
    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?
    • mwaters4's avatar
      mwaters4
      Helper I
      6 years ago

      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

       

  • TeigeGao's avatar
    TeigeGao
    Solution Sage
    7 years ago

    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

    • mwaters4's avatar
      mwaters4
      Helper I
      6 years ago

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