Calculate with context slicer value

[Ju743878]

Hi community,

I struggle with something, let me explain :

I have a table with date and rate like that :

date rate	rate
28.12.2022	0,96
29.12.2022	0,96
30.12.2022	0,96
31.12.2022	0,97
01.01.2023	0,97
02.01.2023	0,97
03.01.2023	0,98

And another table with selling date and amount, like that :

selling date	amount
28.12.2022	100
29.12.2022	100
30.12.2022	100
31.12.2022	100
01.01.2023	100
02.01.2023	100
03.01.2023	100

I need to applied a SUM of amount * rate (SUMX RELATED) but I want to make variable the rate of current YEAR with my slicer date between, for i.e. 
If I put my slicer from 28.12.2022 to 03.01.2022, I need a measure that perform the rate of the RELATED row from 2022 rows, but for 2023 rows, I want to apply the rate of the MAX SELECTED date (0,98 for 03.01.2023 in this sample).

So I will retreive thoses value for my sample :

selling date	amount	SAMPLE
28.12.2022	100	96
29.12.2022	100	96
30.12.2022	100	96
31.12.2022	100	98
01.01.2023	100	98
02.01.2023	100	98
03.01.2023	100	98

KPI = 680.

If I apply my slicer from 28.12.2022 to 02.01.2022, the rate for 2023 rows should be 0,97. 

I assumed that for past year I already have the good rate in my column each time, the variabilization applied only in CURRENt year.

Someone can help me ? In DAX TABULAR !

Thanks in advance ! 

Julien

[Ju7438 · Accepted solution]

I have found the solution :

Calculated Rate =

SWITCH(

    TRUE(),

    YEAR(MAX('Table (2)'[selling date])) < YEAR(TODAY()),

        CALCULATE(SUM('Table'[rate]),   FILTER(ALLSELECTED('Table'),'Table'[date rate] = SELECTEDVALUE('Table (2)'[selling date]))),

    YEAR(MAX('Table (2)'[selling date])) = YEAR(TODAY()),

        CALCULATE(MAX('Table'[rate]),   FILTER(ALLSELECTED('Table'),YEAR('Table'[date rate]) = YEAR(SELECTEDVALUE('Table (2)'[selling date])))

    )

)

For make dynamic the date context, and :

TEST =

AVERAGEX (

    VALUES('Table (2)'[selling date]),

    SUM('Table (2)'[amount]) * [Calculated Rate]

)

To get te right result in table / matrix.

Thanks a lot for the input, I accept it as solution.

BR

Julien

[Anonymous]

Hi @Ju743878 ,

According to your description, here are my steps you can follow as a solution.

(1) My test data is the same as yours.

(2) We can create a measure. 

Measure = 
SWITCH(TRUE(),
YEAR(MAX('Table (2)'[selling date]))=2022 ,CALCULATE(SUM('Table'[rate]),FILTER(ALLSELECTED('Table'),'Table'[date rate]=SELECTEDVALUE('Table (2)'[selling date]))),
YEAR(MAX('Table (2)'[selling date]))=2023 ,CALCULATE(max('Table'[rate]),FILTER(ALLSELECTED('Table'),YEAR('Table'[date rate])=YEAR(SELECTEDVALUE('Table (2)'[selling date])))))

(3) Then the result is as follows.

Best Regards,

Neeko Tang

If this post  helps, then please consider Accept it as the solution  to help the other members find it more quickly. 

[Ju7438]

Hi @Anonymous ,

First of all thanks for your time and help, unfortunatelly it doesnt fit with my needs, the value display correctly what we want but it could not be use in SUMX, see below :

TEST = SUMX('Table (2)', 'Table (2)'[amount] * [Measure])

All values for 2023 rows should be equal to 98 for max selected date (2023-01-03)...

Thanks again for your time, if you have any idea it would be appreciate !

BR

Julien