Forum Discussion
CONNECT two tables using an AUXILIAR CALENDAR
I want to to connect two tables. The first one is a historical annual dates table organized month by month; the second one a 2022-daily table.
MY Objective is just make a graphic that shows months on the x-axis. For connecting the two mentioned tables, i will creat a new calendar table. Is It right?
Any suggestions?
Hi Bluefish ,
Suppose you have the following example data.
Historical Table Daily Table
Then please add a new column to the Historical Table.
MonthYear = [Year] & " " & [Month]Then create the calendar table and inter-table relationships
CALENDAR = ADDCOLUMNS ( CALENDAR ( DATE ( 2020, 12, 1 ), TODAY () ), "Month_Year", FORMAT ( [Date], "yyyy mmm" ), "YM_Num", FORMAT ( [Date], "yyyymm" ) )If the problem is still not resolved, please provide detailed error information or the expected result you expect. Let me know immediately, looking forward to your reply.
Best Regards,
Winniz
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
2 Replies
- amitchandak
Super User
Bluefish , This is my approach, I create a month start date or month-end date using the month year and join it date table along with other table
I discussed it at a few places
Distributing/Allocating the Monthly Target(Convert to Daily Target): Measure ( Daily/MTD): https://community.powerbi.com/t5/Community-Blog/Power-BI-Distributing-Allocating-the-Monthly-Target-Convert-to/ba-p/1657798
v-kkf-msftCommunity Support
Hi Bluefish ,
Suppose you have the following example data.
Historical Table Daily Table
Then please add a new column to the Historical Table.
MonthYear = [Year] & " " & [Month]Then create the calendar table and inter-table relationships
CALENDAR = ADDCOLUMNS ( CALENDAR ( DATE ( 2020, 12, 1 ), TODAY () ), "Month_Year", FORMAT ( [Date], "yyyy mmm" ), "YM_Num", FORMAT ( [Date], "yyyymm" ) )If the problem is still not resolved, please provide detailed error information or the expected result you expect. Let me know immediately, looking forward to your reply.
Best Regards,
Winniz
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
