Forum Discussion
WTD MTD YTD table
Hello community.
Currently we are migrating data from Snowflake to dataflow/datamart. We have several dashboards where we are using the above manual table as a reference table. Is there a way to create a universal Time Period table that we can reference instead of creating manual tables each time?
One of the ways was to either create exce file or create separate table from SF and use that but I was wondering if there are other ways people use as best practice?
Thank you.
Hi! Here is a blog I wrote about my favorite date ref table from SQLBI. There is a link to their DAX table in my article. The Custom Date Table Edition – Power BI with Me
Once you have a robust date table you can use DAX time intelligence functions to create your YTD, QTD, MTD, R7, etc. measures.
2 Replies
- audreygerred
Super User
Hi! Here is a blog I wrote about my favorite date ref table from SQLBI. There is a link to their DAX table in my article. The Custom Date Table Edition – Power BI with Me
Once you have a robust date table you can use DAX time intelligence functions to create your YTD, QTD, MTD, R7, etc. measures.
- Bibiano_Geraldo
Hi Anonymous ,
You can create a date table directly in Power BI using DAX. This table can include all the necessary time periods like YTD, QTD, MTD, etc. Once created, you can use this table across multiple reports.
DateTable = ADDCOLUMNS ( CALENDAR (DATE(2020, 1, 1), DATE(2025, 12, 31)), "Year", YEAR([Date]), "Month", MONTH([Date]), "Quarter", QUARTER([Date]), "MonthName", FORMAT([Date], "MMMM"), "QuarterName", "Q" & QUARTER([Date]) )You can modify first date and last date to be dynamic.
