Forum Discussion

PoPQ's avatar
PoPQ
Frequent Visitor
4 years ago
Solved

Compare values to same month in specific year

I'm interested in creating a measure to compare daily values aggregated to the month with the same month in 2019 as a % difference. For instance, I'd like to compare the sum of December 2021 'thruput' with the same value for December 2019, July 2020 vs. July 2019, etc. I'm not sure I can do this with a simple samperiodlastyear calculation because I always want the comparison year to be fixed at 2019. Data can be found here.

Need Help
  • v-kkf-msft's avatar
    v-kkf-msft
    4 years ago

    Hi PoPQ ,

     

    Please try the following formula:

     

    Measure = 
CALCULATE (
    SUM ( 'TSA_thruput xlsx - Sheet1'[Thruput] ),
    FILTER (
        ALL ( 'TSA_thruput xlsx - Sheet1' ),
        'TSA_thruput xlsx - Sheet1'[Date].[Month]
            = MAX ( 'TSA_thruput xlsx - Sheet1'[Date].[Month] )
            && 'TSA_thruput xlsx - Sheet1'[Date].[Year] = 2019
    )
)

     

    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.

7 Replies

  • mh2587's avatar
    mh2587
    Icon for Super User rankSuper User
    4 years ago

    = calculate(sum[value],DATEADD(DateTime[DateKey],-2,year)

    • PoPQ's avatar
      PoPQ
      Frequent Visitor
      4 years ago

      thanks mh2587 for the quick reply. that almost works but it is relative, where I want the comparison year to be 2019 regardless of whether I'm comparing a 2020, 2021 or 2022 month value to it.

  • mh2587's avatar
    mh2587
    Icon for Super User rankSuper User
    4 years ago

    Measure 1 2020= calculate(sum[value],DATEADD(DateTime[DateKey],-1,year)
    Measure 2 2021= calculate(sum[value],DATEADD(DateTime[DateKey],-2,year)
    Measure 3 2022= calculate(sum[value],DATEADD(DateTime[DateKey],-3,year)

    • PoPQ's avatar
      PoPQ
      Frequent Visitor
      4 years ago

      I'm hoping to solve this as single measure so I can display it as a single trendline over the span of three years.

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

        Hi PoPQ ,

         

        Please try the following formula:

         

        Measure = 
CALCULATE (
    SUM ( 'TSA_thruput xlsx - Sheet1'[Thruput] ),
    FILTER (
        ALL ( 'TSA_thruput xlsx - Sheet1' ),
        'TSA_thruput xlsx - Sheet1'[Date].[Month]
            = MAX ( 'TSA_thruput xlsx - Sheet1'[Date].[Month] )
            && 'TSA_thruput xlsx - Sheet1'[Date].[Year] = 2019
    )
)

         

        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.