Forum Discussion
Merge two date-based tables with summary from both
I have two tables with (different) dates for each entry and each table has some Number coloums I need to summarize into a hierarchy of year/date
So one table finance with coloums "Date" and "Amount" and one table "Bookings" with "Delivery date" and "Billable".
I want a visual with a year/Month date coloumn and the monthly sums of "Billable" and "Amount"
By all guides I found until now, I cannot get this wo work.
When I build a relationship I loose the hierarchy.
When I try to merge the two tables into a new one I loose either the Amount or the Billable coloumn.
Please help me out with this one.
You need to create a dedicated date table that sits over the top of both. You can create a basic one with some code like this.
Dates = VAR DateRange = CALENDARAUTO() RETURN ADDCOLUMNS( DateRange, "Year", YEAR ( [Date] ), "Month Name", FORMAT ( [Date], "mmmm"), "MonthNum", MONTH ( [Date] ), "Month Year", FORMAT ( [Date], "mmm-yyyy"), "YearMonthNum", YEAR ( [Date] ) * 100 + MONTH ( [Date] ), "QTR Year", "Q" & FORMAT ( [date],"Q-yyyy" ), "YearQtrNum", YEAR ( [Date] ) *100 + VALUE ( FORMAT ( [Date], "Q" )) )You create this as a new table in your model. Then you link the date from from the Dates table to the date columns in your fact tables. You use the fields from the dates able like year and month to do your summarizing.
2 Replies
wolfgangkb-work you should create a date dimension table in your model, you can follow my post to create one Create a basic Date table in your data model for Time Intelligence calculations | PeryTUS IT Solutions, and in this table, you can create the hierarchy the way you want.
Set the relationship of this table with your other two tables, in visuals, use hierarchy from this date dimension and values from the other two tables. This is the right way to create the report.
Check my latest blog post Comparing Selected Client With Other Top N Clients | PeryTUS I would ❤ Kudos if my solution helped. 👉 If you can spend time posting the question, you can also make efforts to give Kudos to whoever helped to solve your problem. It is a token of appreciation!
⚡Visit us at https://perytus.com, your one-stop-shop for Power BI-related projects/training/consultancy.⚡
You need to create a dedicated date table that sits over the top of both. You can create a basic one with some code like this.
Dates = VAR DateRange = CALENDARAUTO() RETURN ADDCOLUMNS( DateRange, "Year", YEAR ( [Date] ), "Month Name", FORMAT ( [Date], "mmmm"), "MonthNum", MONTH ( [Date] ), "Month Year", FORMAT ( [Date], "mmm-yyyy"), "YearMonthNum", YEAR ( [Date] ) * 100 + MONTH ( [Date] ), "QTR Year", "Q" & FORMAT ( [date],"Q-yyyy" ), "YearQtrNum", YEAR ( [Date] ) *100 + VALUE ( FORMAT ( [Date], "Q" )) )You create this as a new table in your model. Then you link the date from from the Dates table to the date columns in your fact tables. You use the fields from the dates able like year and month to do your summarizing.
