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
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
Solved! Go to Solution.
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
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?
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