Data on Date Range and Customer

[Anonymous]

Hi , i am trying to add to my transaction table , the price from the price list.

transactiontbl:

Customer, Date, Amount

PriceListtbl:

[From Date],[To Date],[Customer],[Price]

please help me, to add the price to transactiontbl

[Anonymous · Accepted solution]

@Anonymous

Implement the below DAX and let me know if this is what you're looking for?

Create a new column applying the below DAX.

Price=
         CALCULATE( AVERAGE( PriceListtbl[Price] ),
                              FILTER( PriceListtbl,
                                          PriceListtbl[FromDate] <= ( transactiontbl[Date] )
                                          && PriceListtbl[ToDate] >= ( transactiontbl[Date] )
                                          && PriceListtbl[Customer] = ( transactiontbl[Customer] )
                                          )
                            )

Then taking it a step further to work out the Amount * Price per Customer, ignoring the date (although the Price is reflected by Date period in the first calculation) - see below:

Create a new table apply the below DAX:

tPRICE =
          GROUPBY( transactiontbl, transactiontbl[Customer],
                           "PRICEPRICE", SUMX( CURRENTGROUP(), transactiontbl[cPrice] * transactiontbl[Amount] )
                          )

Note: you can rename the column/table to anything you want.

[Anonymous]

Thank you, 

Is there an option to do it in power query?