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