Joining data from two tables
Calling all PBI experts...
I have, what on the surface, looks a straightforward problem but I just can get the right solution.
I have two tables (customer & transactions) which are related by one column - the customer code.
I want to combine the results into one table and agregate the transactions over the periods like this:
So the two questions are...a) how do I get the revenue from my transaction table summarised by year & month and drop it into the appropriate columns (year / month comes from the customer table) and b) how do I apply filtering to the resulting table without losing future periods? i.e. if I create a slicer on the month and select month 2 how do I avoid losing future data from the results?
Any help would be much appreciated.
Hi AndyTrezise,
Please check out the demo in the attachment.
Since there aren't any continuous dates, I think a date table is needed. For example, it will be hard to get the next month of December 2018 without a date table.
1. Create a date table.
Calendar = CALENDARAUTO()
2. Create a new column of table Customer.
date = date([Year], [Month],1)
3. Establish relationships and change the "Filter direction".
4. Create several measures.
Revenue This Period = CALCULATE ( SUM ( 'transaction'[Revenue] ), FILTER ( ALL ( 'transaction' ), 'transaction'[Customer] = MIN ( 'customer'[Customer] ) && YEAR ( 'transaction'[Date] ) = MIN ( 'customer'[Year] ) && MONTH ( 'transaction'[Date] ) = MIN ( 'customer'[Month] ) ), ALL ( 'customer' ) )Revenue P+1 = CALCULATE ( SUM ( 'transaction'[Revenue] ), FILTER ( ALL ( 'transaction' ), 'transaction'[Customer] = MIN ( 'customer'[Customer] ) ), NEXTMONTH ( 'Calendar'[Date] ), ALL ( customer ) )Revenue P+2 = CALCULATE ( SUM ( 'transaction'[Revenue] ), FILTER ( ALL ( 'transaction' ), 'transaction'[Customer] = MIN ( 'customer'[Customer] ) ), DATESINPERIOD ( 'Calendar'[Date], EOMONTH ( MIN ( 'Calendar'[Date] ), 2 ), -1, MONTH ), ALL ( customer ) )Best Regards,
Dale
