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.

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

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.⚡