Dax search for value based on customer and date range

[Anonymous]

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!!!

[v-yulgu-msft]

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

[Anonymous]

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

[v-yulgu-msft]

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