Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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.
Solved! Go to 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
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
That's fantastic...thank you so much for your help - that's just what I needed.
Cheers
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
107 | |
98 | |
78 | |
65 | |
53 |
User | Count |
---|---|
144 | |
103 | |
98 | |
85 | |
64 |