Forum Discussion

JohnLow's avatar
JohnLow
Helper I
5 years ago
Solved

Previous Year query

Hi all,    I'm running into an issue when trying to calculate a budget for the previous year. I have 3 columns and I've created the following new column below, I just get blank values however. Coul...
Need Help
  • Tahreem24's avatar
    Tahreem24
    5 years ago

    JohnLow  try to create any measures among these:

    BudgetTotalPreviousYear MEASURE = CALCULATE(SUM('Table'[Budget total]),PREVIOUSYEAR('Table'[YearPeriod]))

    Or try this:

    BudgetTotalPreviousYear MEASURE= CALCULATE(SUM('Table'[Budget total]),DATEADD('Table'[YearPeriod],-1,YEAR))

     

  • Anonymous's avatar
    Anonymous
    5 years ago

    Hi JohnLow,

    Have you tried to directly use the date function to calculate the previous data for calculating?

    BudgetTotalPreviousYear =
VAR curr = 'Table'[YearPeriod]
RETURN
    CALCULATE (
        SUM ( 'Table'[Budget total] ),
        FILTER (
            'Table',
            [YearPeriod]
                = DATE ( YEAR ( curr ) - 1, MONTH ( curr ), DAY ( curr ) )
        )
    )

    Regards,
    Xiaoxin Sheng

JohnLow's avatar
JohnLow
Helper I
5 years ago

Thanks. I'm just wondering about the measure in a line chart now. It doesn't seem to work as expected. 

I've attached the file. Thanks again. 

File 

Anonymous's avatar
Anonymous
Not applicable
5 years ago

Hi JohnLow,

Have you tried to directly use the date function to calculate the previous data for calculating?

BudgetTotalPreviousYear =
VAR curr = 'Table'[YearPeriod]
RETURN
    CALCULATE (
        SUM ( 'Table'[Budget total] ),
        FILTER (
            'Table',
            [YearPeriod]
                = DATE ( YEAR ( curr ) - 1, MONTH ( curr ), DAY ( curr ) )
        )
    )

Regards,
Xiaoxin Sheng