Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
2 years ago
Solved

Calculate with context slicer value

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...
Need Help
Tips and Tricks
Tutorial Requests
  • Ju7438's avatar
    Ju7438
    2 years ago

    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's avatar
Anonymous
Not applicable
2 years ago

Hi Anonymous ,

 

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's avatar
Ju7438
Regular Visitor
2 years ago

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