Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
2 years ago
Solved

Year to Date Percentage Change Calculation

I want to show the year to date % change as a line on my chart. I want to show this only for the months in 2024 and my reference data is December 2023. The source table shows the revenue derived from...
Need Help
  • Anonymous's avatar
    Anonymous
    2 years ago

    Hi Anonymous 

     

    I made some changes to the code for the new table.

     

    EarlyTotal = 
var CurrentYear = CALCULATE(MAX('Table'[Date]), ALL('Table'))
var earlyMaxDate = CALCULATE(MAX('Table'[Date]), FILTER(ALL('Table'), YEAR('Table'[Date]) = YEAR(CurrentYear) - 1 ))
var EarlyYearTotal = 
    CALCULATE(
        SUM('Table'[ Amount]), 
        FILTER(
            ALL('Table'), 
            YEAR('Table'[Date]) = YEAR(CurrentYear) - 1 
            && 
            MONTH('Table'[Date]) = MONTH(earlyMaxDate)
        )
    )
RETURN SUMMARIZE('Table', "EarlyYearTotal", EarlyYearTotal)

     

    You can check it out.

     

    Regards,

    Nono Chen

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

Anonymous's avatar
Anonymous
Not applicable
2 years ago

I tried it but threw the following error:

I replaced [Amount] in Calculate function with SUM(Table Name[Amount]) since the column itself doesn't appear in Calculate function. And I also used Monh No column from my Calendar table for Calendar [Monthnum], which I believe, what you've intended for. Other than these, everyting is same.

G_CR_23's avatar
G_CR_23
Icon for Helper I rankHelper I
2 years ago

Hi Anonymous ,
i've tested measure and mofied it, so it can bel more dynamic.

Here the measures i've created

Amount = SUM('Table'[ Amount ])
 
Delta % =
var year_ = CALCULATE(MAX('Calendar'[Year]),ALL('Calendar'))
var selectedyear = MAX('Calendar'[Year])
var monthnum = MAX('Calendar'[Month NO])
var amount_ly = CALCULATE([Amount],ALL('Calendar'),'Calendar'[Year] = year_-1,'Calendar'[Month NO]<=monthnum)
var amount_cy = CALCULATE([Amount],ALL('Calendar'),'Calendar'[Year] = year_,'Calendar'[Month NO]<=monthnum)
var delta = DIVIDE(amount_cy-amount_ly,amount_ly,BLANK())
var result =
IF(
    selectedyear = year_-1, BLANK(),
    delta)

return
result
 
 
my calendar columns are this
Calendar = CALENDAR(MIN('Table'[Date]),MAX('Table'[Date]))
Month = FORMAT('Calendar'[Date],"mmm")
Month NO = MONTH('Calendar'[Date]) (integer)
Year = YEAR('Calendar'[Date]) (integer)
 
in this way measure works even if max year changes (e.g. 2025 vs 2024)
 
i hope this help you!