Add values from two tables that share a date

[kman42]

I have two tables. 

Table 1:

Date	Amount
1/1/2024	50
2/1/2024	60
3/1/2024	70
4/1/2024	80
5/1/2024	90

Table 2:

Date	Offset
2/1/2024	10
3/1/2024	20
4/1/2024	30

I'd like to get a table like this:

Date	Amount + Offset
1/1/2024	50
2/1/2024	70
3/1/2024	90
4/1/2024	110
5/1/2024	90

I think this should be easy, but I've spent several hours without figuring it out. Any help would be appreciated.

Thanks!

[MattAllington · Accepted solution]

There are various approaches. Regardless of which approach, you should get a calendar table. 
https://exceleratorbi.com.au/power-bi-calendar-tables/

then you can either join the calendar table to bith of the tables to have (one to many), use the calendar table in your visual and then write a measure = sum(table1[amount]) + sum(table2[offset])

or, if it were me, and if the tables actually look like those you posted (ie, there's nothing your holding back and not telling me), I would do the following 

in power query, create transform table 1 to be like

date, attribute, amount

1/1/24, "Amount", 450

etc

table 2, exactly the same headers but put "offset" as the attribute 

append the 2 tables together and load a single table

still join to the calendar table as above.

you can simply drag the amount column into the values section and even use the attribute column in your visual. 

[HamedM1125 · Accepted solution]

Hello @kman42 

You can acheive this doing by following steps:

1. Create a Date Table by going to modelling and in the new table add the following dax:

Date =

ADDCOLUMNS (

CALENDAR( "1/1/2024", "12/31/2024"),

"ShortDate", FORMAT([Date], "DD-MMM"),

"Date_Date", FORMAT([Date], "MM/DD/YY"),

"DateAsInteger", FORMAT ( [Date], "YYYYMMDD" ),

"Year", YEAR ( [Date] ),

"Monthnumber", FORMAT ( [Date], "MM" ),

"YearMonthnumber", FORMAT ( [Date], "YYYYMM" ),

"YearMonthShort", FORMAT ( [Date], "YYYY/mmm" ),

"MonthNameShort", FORMAT ( [Date], "mmm" ),

"MonthNameLong", FORMAT ( [Date], "mmmm" ),

"DayOfWeekNumber", WEEKDAY ( [Date] ),

"DayOfWeek", FORMAT ( [Date], "dddd" ),

"DayOfWeekShort", FORMAT ( [Date], "ddd" ),

"Quarter", "Q" & FORMAT ( [Date], "Q" ),

"YearQuarter", FORMAT ( [Date], "YYYY" ) & "/Q" & FORMAT ( [Date], "Q" )

)

2. Create a relantionship between Date and other tables:

3. Create a measure:

Amount + Offset =

Var A = SUM('Table 1'[Amount])

VAR B = SUM('Table 2'[Offset])

RETURN

A + B

4. Result:

Let me know if you got this. Kudos!

[FreemanZ · Accepted solution]

hi @kman42 ,

To start less stressfully, you can add a calculated column in table1 like this:

Column =

[Amount] +

LOOKUPVALUE(

    table2[offset],

    table2[date],

    table1[date]

)

[HamedM1125 · Accepted solution]

Hello @kman42 

You can acheive this doing by following steps:

1. Create a Date Table by going to modelling and in the new table add the following dax:

Date =

ADDCOLUMNS (

CALENDAR( "1/1/2024", "12/31/2024"),

"ShortDate", FORMAT([Date], "DD-MMM"),

"Date_Date", FORMAT([Date], "MM/DD/YY"),

"DateAsInteger", FORMAT ( [Date], "YYYYMMDD" ),

"Year", YEAR ( [Date] ),

"Monthnumber", FORMAT ( [Date], "MM" ),

"YearMonthnumber", FORMAT ( [Date], "YYYYMM" ),

"YearMonthShort", FORMAT ( [Date], "YYYY/mmm" ),

"MonthNameShort", FORMAT ( [Date], "mmm" ),

"MonthNameLong", FORMAT ( [Date], "mmmm" ),

"DayOfWeekNumber", WEEKDAY ( [Date] ),

"DayOfWeek", FORMAT ( [Date], "dddd" ),

"DayOfWeekShort", FORMAT ( [Date], "ddd" ),

"Quarter", "Q" & FORMAT ( [Date], "Q" ),

"YearQuarter", FORMAT ( [Date], "YYYY" ) & "/Q" & FORMAT ( [Date], "Q" )

)

2. Create a relantionship between Date and other tables:

3. Create a measure:

Amount + Offset =

Var A = SUM('Table 1'[Amount])

VAR B = SUM('Table 2'[Offset])

RETURN

A + B

4. Result:

Let me know if you got this. Kudos!

[MattAllington · Accepted solution]

There are various approaches. Regardless of which approach, you should get a calendar table. 
https://exceleratorbi.com.au/power-bi-calendar-tables/

then you can either join the calendar table to bith of the tables to have (one to many), use the calendar table in your visual and then write a measure = sum(table1[amount]) + sum(table2[offset])

or, if it were me, and if the tables actually look like those you posted (ie, there's nothing your holding back and not telling me), I would do the following 

in power query, create transform table 1 to be like

date, attribute, amount

1/1/24, "Amount", 450

etc

table 2, exactly the same headers but put "offset" as the attribute 

append the 2 tables together and load a single table

still join to the calendar table as above.

you can simply drag the amount column into the values section and even use the attribute column in your visual.