Sales between two different dates but the dates have to be defined by other columns.

[Young_G_Han]

  Dear Sir

Dax formula is a very difficult but fun tool for a simple user. I admire the Kudos who help the light users, thank you in advance.

I need to sum transactions between two dates.

But my problem is that I need to apply different periods for each customer.

For example,

For AAA customer, I have to take the periods from another table but that must be code 'A' and '1'.

Let's say the period should be 01/01/2023 to 01/10/2023. This is already in a period table.

For BBB customer, I have to take the periods from the same table but that must be code 'B' and '2'.

Likely said the period should be 01/10/2022 to 01/05/2023. This is also in the period table.

Following is the table sample.

Transactions

Customer   Item   Transaction Q'ty  Transaction Date

AAA               a              100              01/05/2023 

AAA               b               10               01/01/2022

BBB                a                5                01/01/2023

BBB                b               20               01/08/2022

Date

Customer   Start Date       End Date        Code

AAA            01/01/2023    01/10/2023       1

AAA            01/01/2022    31/12/2022       2

BBB             01/10/2022    01/05/2023       1

BBB             01/10/2021    30/09/2022       2

Probably I need to create a table or I can use a measure that can create table visualization.

The result I need is...

Customer   Transaction   Item    Code

AAA            100                 a          1

AAA              10                 b          2

BBB               5                   a          1

BBB               20                 b          2

Is this possible? It is okay whether I need to create a table or a measure.

Please help.

[danextian]

Hi @Young_G_Han 

First you need to create a item/code match in your period/dates table:

Code2 = 
SWITCH ( 'Date'[Code], 1, "a", 2, "b" )

You can then use the calc column formula below to get the sales amount from anothe table based on a certain start and end dates:

Qty = 
CALCULATE (
    SUM ( Sales[Transaction Qty] ),
    FILTER (
        Sales,
        Sales[Transaction Date] >= EARLIER ( 'Date'[Start Date] )
            && Sales[Transaction Date] <= EARLIER ( 'Date'[End Date] )
            && 'Sales'[Customer] = EARLIER ( 'Date'[Customer] )
            && Sales[Item] = EARLIER ( 'Date'[Code2] )
    )
)

Please see attached pbix for reference.

[Young_G_Han]

  Dear Sir

Thank you. This is very helpful me to understand date and add column.

[amitchandak · Accepted solution]

@Young_G_Han , You can add Code in table one as a new column and then join these two table

Code column in transaction

Maxx(filter(Date, Date[Customer] = Transactions[Customer] && Transactions[Transaction Date] >= Date[Start Date] &&  Transactions[Transaction Date] <= Date[End Date] ) , Date[Code])

[Young_G_Han]

  Dear Sir

It is working finely. I could add more columns as filter, this is very useful.

Thank you very much.

Regards,

Young.