Forum Discussion

Fusilier2's avatar
Fusilier2
Icon for Helper V rankHelper V
1 year ago
Solved

Help with year on year calculation

Hi,

Hope somebody ca nhelp me?

I have this working measure:

 

% Count Agree = DIVIDE( SUM('Append'[Count Agree]) , CALCULATE(SUM('Append'[Answer count]),all('Append'[Aggregated answers])))
 
There is a year column in the 'Append' table (with two years worth of data: 2023 and 2024) linked to a calendar table.
I want to calculate a year on year difference? I presume I would need two measures one for 2024 and one for 2023 and deduct one from the other. So, how would I adapt the existing measure to create one measure for 2024 and one for 2023?
 
Thanks!
Need Help
  • Anonymous's avatar
    Anonymous
    1 year ago

    Hi Fusilier2 

     

    Thanks for the reply from SachinNandanwar .

     

    Fusilier2 , the following testing is for your reference. 

     

    My sample:

     

    Create two measures as follow.

    LastYear = 
VAR _LastYear = MAX([Year]) - 1
VAR _sum = CALCULATE(SUM('Table'[Value]), FILTER('Table', [Year] = _LastYear))
RETURN
_sum

     

    Divide = 
VAR _MaxYear = MAX([Year])
VAR _sum = CALCULATE(SUM('Table'[Value]), FILTER('Table', [Year] = _MaxYear))
RETURN
DIVIDE(_sum, [LastYear])

     

    Output:

     

    If the data structure I use is different from what you use, please provide some sample data and the expected results based on the sample data so that we can better help you. How to provide sample data in the Power BI Forum - Microsoft Fabric Community  Or show them as screenshots or pbix. Please remove any sensitive data in advance. If uploading pbix files please do not log into your account.

     

    Best Regards,
    Yulia Xu

     

    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

4 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    1 year ago

    Hi Fusilier2 

     

    Thanks for the reply from SachinNandanwar .

     

    Fusilier2 , the following testing is for your reference. 

     

    My sample:

     

    Create two measures as follow.

    LastYear = 
VAR _LastYear = MAX([Year]) - 1
VAR _sum = CALCULATE(SUM('Table'[Value]), FILTER('Table', [Year] = _LastYear))
RETURN
_sum

     

    Divide = 
VAR _MaxYear = MAX([Year])
VAR _sum = CALCULATE(SUM('Table'[Value]), FILTER('Table', [Year] = _MaxYear))
RETURN
DIVIDE(_sum, [LastYear])

     

    Output:

     

    If the data structure I use is different from what you use, please provide some sample data and the expected results based on the sample data so that we can better help you. How to provide sample data in the Power BI Forum - Microsoft Fabric Community  Or show them as screenshots or pbix. Please remove any sensitive data in advance. If uploading pbix files please do not log into your account.

     

    Best Regards,
    Yulia Xu

     

    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

  • Ashish_Mathur's avatar
    Ashish_Mathur
    Icon for Super User rankSuper User
    1 year ago

    Hi,

    The relationship should be between the Date column of the Append table to the Date column of the Calendar Table.  In the Calendar table, create a Year column and drag Year from the Calendar table to your visuals/filtes/slicers.  Select 2024.  Write these measures

    % count agree LY = calculate([% count agree],previousyear(calendar[date]))

    Hope this helps.

  • Anonymous's avatar
    Anonymous
    Not applicable
    1 year ago

    Hi Fusilier2 

     

    Do the methods offered so far solve your problem? If so, could you please mark helpful answers as solutions? This will help more users who are facing the similar difficulties. Thank you!

     

    Please feel free to let me know if the problem persists.

     

    Best Regards,
    Yulia Xu

  • SachinNandanwar's avatar
    SachinNandanwar
    Icon for Impactful Individual rankImpactful Individual
    1 year ago

    You could use SAMPLEPERIODLASTYEAR function

    YourMeasure= CALCULATE(
[% Count Agree],
SAMEPERIODLASTYEAR('Calendar'[Date])
)