[DAX] How to apply a multiplier on a Parameter based on a Date and another parameter

[BunzietheBoo]

Hello all, 

First post for me here on a problem I have for a few days.
I am working on a dashboard displaying sales data for a bunch of shops.
To be properly interpreted, the sales numbers has to be adjusted by a multiplier.
This multiplier is different for every shop and is changing through time.

The multiplier values are stored in a "Multiplier" table  :

ShopID	Date	Multiplier
1	01.01.2018	1
1	01.01.2020	1.2
2	01.01.2018	1.1
3	02.03.2019	1.05

On the other end, the sales values are stored in another "Sales"table :

ShopID	Date	SalesNb
1	01.01.2018	545
2	01.01.2018	362
3	01.01.2018	421
1	02.01.2018	532
2	02.01.2018	350
(...)	(...)	(...)

I also have a "DateTable" where I store all the date input of my data model and a "Shop" Table.
Regarding the relationships : 
"DateTable"    1 ---->-----*    "Sales"
"Shop"            1 ---->---- *    "Sales"
"Shop"            1 ---->-----*    "Multiplier"
"DateTable"    1 ---->---- *    "Multiplier"

My goal is to have a measure that return for a given asset and for a given date, the multiplier that should be apply to the Sales number.

This measure will then be used in a SWITCH measure to allow the user to pick if they want the adjusted number or not.
Good luck and tell me if you want more information !

[PaulOlding]

Hi @BunzietheBoo 

Have you thought about adding the Multiplier to the Sales table, either in Power Query or the source?

From a modelling perspective that's where it should go if you're going to use it in a measure.

The resulting measure will also perform better - something to consider if you have a large amount of data.

[PaulOlding · Accepted solution]

Some DAX to add it as a calculated column to Sales would be 

Multiplier = 
VAR _Shop = Sales[ShopID]
VAR _Date = Sales[Date]
VAR _Latest = 
TOPN(
    1,
    FILTER(Multiplier, Multiplier[Date] <= _Date && Multiplier[ShopID] = _Shop),
    Multiplier[Date], DESC
)
RETURN
MAXX(_Latest, Multiplier[Multiplier])

The measure is then relatively simple

Sales with Multiplier = 
SUMX(
    Sales,
    Sales[SalesNb] * Sales[Multiplier]
)