Forum Discussion

anjanikumar's avatar
anjanikumar
Helper III
4 years ago

How can we make dynamically(Automatically) years can be selected in below formula ?

How can we make dynamically(Automatically) years can be selected in below formula ?

 

Measure = CALCULATE ( SUM ( table[AMOUNT_USD] ), FILTER ( table, table[SCENARIO] = "ACTUAL" && table[CURRENT_YEAR] = "2022" ) ) - CALCULATE ( SUM ( table[AMOUNT_USD] ), FILTER ( table, table[SCENARIO] = "ACTUAL" && table[CURRENT_YEAR] = "2021" ) )

 

please help me.

5 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    4 years ago

    For the First, Calculate function use This: -

    YEAR( TODAY()) instead of "2022"
    and for the second use This : - 
    YEAR( date(year(TODAY())-1,MONTH(TODAY()),DAY(TODAY()))) instead of "2021"
    • anjanikumar's avatar
      anjanikumar
      Helper III
      4 years ago

      VPY = CALCULATE(BUF_DATAMART_V[AMOUNT_USD_ACTUAL],BUF_DATAMART_V[CURRENT_YEAR]=YEAR(TODAY())-2)-CALCULATE(BUF_DATAMART_V[AMOUNT_USD_ACTUAL],BUF_DATAMART_V[CURRENT_YEAR]=YEAR(TODAY())-3)

       

      I dont want manually change the years like -2 and like -3, how to dynamically update ?

       

  • v-kkf-msft's avatar
    v-kkf-msft
    Community Support
    4 years ago

    Hi anjanikumar ,

     

    Not sure I understand your needs correctly. If you want to calculate the difference in amount between current year and the previous year, then try the time intelligence functions, like this:

     

    Measure =
CALCULATE ( SUM ( 'Table'[AMOUNT_USD] ), DATESYTD ( Dates[Date] ) )
    - CALCULATE ( SUM ( 'Table'[AMOUNT_USD] ), PREVIOUSYEAR ( Dates[Date] ) )

     

    If the problem is still not resolved, please provide detailed error information or the expected result you expect. Let me know immediately, looking forward to your reply.
    Best Regards,
    Winniz
    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

     

    • anjanikumar's avatar
      anjanikumar
      Helper III
      4 years ago

      Hi , 

       

      Thank you for your support in Advance !!

       

      after that i have created below measure but manually need to change this year values -2 and -3 in this measure , i want change automatically that years (like subtraction -2 and -3).Please help me.

       

      VPY = CALCULATE(BUF_DATAMART_V[AMOUNT_USD_ACTUAL],BUF_DATAMART_V[CURRENT_YEAR]=YEAR(TODAY())-2)-CALCULATE(BUF_DATAMART_V[AMOUNT_USD_ACTUAL],BUF_DATAMART_V[CURRENT_YEAR]=YEAR(TODAY())-3)

      • v-kkf-msft's avatar
        v-kkf-msft
        Community Support
        4 years ago

        Hi anjanikumar ,

         

        Maybe you can try the What-if parameter, so you can dynamically change the year based on the parameter value.

         

                

        VPY = 
CALCULATE (
    BUF_DATAMART_V[AMOUNT_USD_ACTUAL],
    FILTER ( BUF_DATAMART_V, BUF_DATAMART_V[CURRENT_YEAR] = [Parameter Value] )
)
    - CALCULATE (
        BUF_DATAMART_V[AMOUNT_USD_ACTUAL],
        FILTER ( BUF_DATAMART_V, BUF_DATAMART_V[CURRENT_YEAR] = [Parameter Value] - 1 )
    )

        Best Regards,
        Winniz