Joining data from two tables

[AndyTrezise]

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. 

[v-jiascu-msft · Accepted solution]

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

[AndyTrezise]

That's fantastic...thank you so much for your help - that's just what I needed.

Cheers