Divide columns from different tables with different conditions

[Anonymous]

Hi!

can anyone help me on this?

I have TABLE A with accounts'data like this one:

Account name	Account created at Date (week num.-year)
Alex	3-2020
Max	5-2020
Neil	9-2020

and TABLE B with orders' data like this one:

Account name	Weekly Spent	Order date (week num.-year)
Alex	£5	3-2020
Alex	£10	5-2020
Max	£20	6-2020
Neil	£20	9-2020

These tables are connected by the "account name" field. 

I want to get the "weekly spent"/"cumulative number of customers". This should be the result:

Order date (week num.-year)	3-2020	5-2020	6-2020	9-2020
"weekly spent"/"cumulative number of customers"	(£5/1) = £5	(£10/2)=£5	(£20/2)=£10	(£20/3)=£6.6

I've already calculated the "cumulative number of customers", but when I create the matrix visualization, I'm unable to specificy to divide "weekly spent" from TABLE A by "Cumulative num of customers" from TABLE B, where  "account created date" from TABLE A = "order date" from TABLE B. 

These 2 tables are already connected by "account name" and I'm unable to create a second connection between "order date" and "account created at date".

I hope I was clear.

Thanks"

[lbendlin]

you need a Dates table in your data model. Once you have that you can construct the required filters. 

When does you cumulative customer count start? does it reset every year?

[Anonymous]

Hi!

a Dates table to connect to Table A and Table B? How do I connect it? 

cumulative customer count start from the beginning. No need to reset.

Thanks for your reply

[ryan_mayu · Accepted solution]

@Anonymous 

You need to create a datetime table and create the relationships like the screenshot below.

Then you create a measure to calculate the reslut

result = 
VAR maxdate = max('datetime'[Date])
VAR accumalated = CALCULATE(sum(TableB[Weekly Spent]),FILTER(TableB,'TableB'[Order date (week num.-year)]<=maxdate))
VAR number=CALCULATE(COUNT('TableA'[Account name]),FILTER(all('TableA'),'TableA'[Account created at Date (week num.-year)]<=maxdate))
Return accumalated/number

At last, use the date column from datetime table and the measure you created to show the result that you want.

[Anonymous]

Hi,

thanks for your reply. Table A and Table B are already connected by "account name" and this doesn't allow me to build relationships you showed me and keep them both active.

[v-diye-msft]

Hi @Anonymous 

Based on the solution provided by @ryan_mayu If you'd like to both relationship, you can active one of them and use USERALATIONBSHIP to enable the another one.

[Anonymous]

Hi @v-diye-msft ,

sorry I'm new to Power BI and I struggle to understand the solution. I want to keep both relationships active. Where do I insert the USERLATIONSHIP formula? 

[ryan_mayu]

@Anonymous 

Please see the doc file of userelationship.

https://docs.microsoft.com/en-us/dax/userelationship-function-dax

[ryan_mayu]

@Anonymous 

you need to delete the existing relationship and rebuild new relationships.