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. Could anyone please let me know where I'm going wrong. 

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

The data is attached here

 

Thank you for any help.

 

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

4 Replies

  • Tahreem24's avatar
    Tahreem24
    Super User
    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))

     

    • 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