Forum Discussion

wolfgangkb-work's avatar
wolfgangkb-work
Icon for Helper I rankHelper I
5 years ago
Solved

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"...
Need Help
  • jdbuchanan71's avatar
    jdbuchanan71
    5 years ago

    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.

jdbuchanan71's avatar
jdbuchanan71
Icon for Super User rankSuper User
5 years ago

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.