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 raterate
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 dateamount
28.12.2022100
29.12.2022100
30.12.2022100
31.12.2022100
01.01.2023100
02.01.2023100
03.01.2023100

 

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 dateamountSAMPLE
28.12.202210096
29.12.202210096
30.12.202210096
31.12.202210098
01.01.202310098
02.01.202310098
03.01.202310098

 

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

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

3 Replies

  • 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

  • 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