Hey,
i have 2 tables, customers and sales. customers has the columns customersID and createdOn. a calculated column gets just the year out of the createdOn column. The sales table has the columns customerID and dateOfSale. It has a calculated column for just the year aswell and additionally a measure buyingCustomers with
DISTINCTCOUNT(sales[customerID])
The tables have a 1:N relationship over customerID (the 1 being on the side of the customers table, because every customer can buy multiple teams or not at all).
I can create a column chart with buyingCustomers and the year calculated column which shows the amount of different customers that actually bought something in the last years. I would like to compare those values with the total amount of customers in the same column chart.
For that i created following measure:
TotalCustomers =
CALCULATE(
COUNTA('customers'[createdOn]);
FILTER(
ALL('customers'[Year]);
ISONORAFTER('customers'[Year]; MAX('customers'[Year]); DESC
)
))
In a seperate column chart with the measure TotalCustomers as value and customers[Year] it works as intended, showing me the amount of customers for each year. But since both tables have their own date-context I can't just put them in the same chart.
With customers[year], TotalCustomers and buyingCustomers in the same graph i get the correct values for the total customers per year but buying customers only counts the customers that were created in the same year.
If i use sales[year], TotalCustomers and buyingCustomers in the same graph i get the correct values for the buying customers per year but the value for TotalCustomers is just the newest one for each year and not increasing at all.
I tried using a Calendar table and adding relationships to both other table[dates] but that just gives the latest value for both measures and not adjusted for different years.
Is there a way to somehow make this work in directquery mode? Or is it simply not possible because the different year contexts limits my options?
Thanks for your help 🙂
