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.
Hi everyone,
I have a table with customer conditions like this:
Customer Max no / month From To
customer 1 50 1/1/2018 31/12/2018
customer 2 60 1/1/2018 31/12/2018
customer 1 55 1/1/2019 31/12/2019
then i have a table with numbers per date per customer like this:
Customer Date Number
customer1 1/3/2018 10
customer2 1/3/2018 20
customer1 4/5/2018 25
customer2 20/5/2018 30
....
Then I have a graph per customer and per month/quarter/year where I added the sum of numbers.
Now I also want to know if customers do not have reached there maximum of numbers for the filtered period.
So I want to add "max numbers" from the first table based on the date and customer from the second table.
Can anyone help me please?
Thanks a lot!!!
Hi @Anonymous,
You can generate a calculated table, then, create a graph based on this table.
TB3 = FILTER ( CROSSJOIN ( TB1, TB2 ), TB2[Date] >= TB1[From] && TB2[Date] <= TB1[To] && TB1[Customer] = TB2[CustomerName] )
Please make sure that each column name is not duplicated in TB1 and TB2.
Regards,
Yuliana Gu
Hi v-yulgu-msft
I came pretty far with this. Thanks a lot!
Now, I just have one problem:
Customers have a monthly payment that can very per customer.
f.e. cust1 pays 20 / month, cust2 pays 30 / month
I want to know how much we receive per month for all customers:
f.e. jan 20 > cust2 is joining in feb: start date = 1th of feb
feb 50
With the calculated tabel I can not simply use sum or max (or..) because I then I have too many cases or just the highest...
Is there some kind of measure I can use for this?
Thanks a lot!
Qlaro
Hi @Anonymous,
Now, I just have one problem:
Customers have a monthly payment that can very per customer.
f.e. cust1 pays 20 / month, cust2 pays 30 / month
I want to know how much we receive per month for all customers:
f.e. jan 20 > cust2 is joining in feb: start date = 1th of feb
feb 50
With the sample data in original post, I cannot get above information, such as "cust1 pays 20 / month" and "cust2 is joining in feb". What is your desired output with provided sample data?
Regards,
Yuliana Gu
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.