topic Re: [DAX] How to apply a multiplier on a Parameter based on a Date and another parameter in DAX Commands and Tips

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

BunzietheBoo — Mon, 08 Nov 2021 15:11:08 GMT

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 !

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

PaulOlding — Mon, 08 Nov 2021 17:36:09 GMT

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.

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

PaulOlding — Mon, 08 Nov 2021 17:52:58 GMT

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] )