Previous Year query

[JohnLow]

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.

[Tahreem24 · Accepted solution]

@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))

[Tahreem24 · Accepted solution]

@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]

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 · Accepted solution]

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

[amitchandak]

@JohnLow , This will work as measure not column. Also use date table

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

Power BI — Year on Year with or Without Time Intelligence
https://medium.com/@amitchandak.1978/power-bi-ytd-questions-time-intelligence-1-5-e3174b39f38a
https://www.youtube.com/watch?v=km41KfM_0uA