Look up last year's data by account

[codextero]

Been having a lot of trouble with this problem.

Scenario : Power BI is connected to SSAS via a data model, so I can not create caldulated tables, only measures.

The goal is to be able to compare data from one year vs data from a previous year. The fact_value table containing the data is broken down by accounts within a period, ex.

Account	Year	Period	value
1	2010	1	1
1	2010	2	1
1	2011	1	3
1	2011	2	4
2	2010	1	5
2	2010	2	6
2	2011	1	7
2	2011	2	8

and a dim_period lookup in another table

Date	Year	Period
01/01/2010	2010	1
02/01/2010	2010	2
01/01/2011	2011	1
02/01/2010	2011	2

I tried

Prev_Year_Value= LOOKUPVALUE('FACT_value'[value],'dim_period'[year],(dim_period[year]-1),dim_period[period],dim_period[period],fact_value[account],fact_value[account])

Which didn't work with the error a single value for year in dim_periods could not be determined

Using

calculate('fact_value'[value],sameperiodlastyear('dim_period'[date]))

failed for the same reason. Changing it to sum('fact_value'[value]) returned numbers, but ones that were incorrect.

Just including last year's value in fact_values works, but adds another column to a pretty huge table in production. Any help on how to accomplish this task without relying on the expensive solution?

[v-jiascu-msft]

Hi @codextero,

Please check out the demo in the attachment. There could be two methods.

1. Work with only one table.

Method 1 =
VAR lastYear =
    CALCULATE (
        MAX ( fact_value[Year] ),
        FILTER (
            ALLEXCEPT ( 'fact_value', fact_value[Account] ),
            fact_value[Year] < MAX ( fact_value[Year] )
        )
    )
RETURN
    CALCULATE (
        SUM ( fact_value[value] ),
        FILTER (
            ALLEXCEPT ( 'fact_value', fact_value[Account] ),
            fact_value[Year] = lastYear
        )
    )

2. Create a new column of Fact_value and establish relationship.

Relationship = date([Year],[Period],1)

Method 2 =
CALCULATE ( SUM ( fact_value[value] ), PREVIOUSYEAR ( 'dim_period'[Date] ) )

Best Regards,

Dale

[codextero]

Hi there, your solution is a good start and is quite helpful.

But the problem is that it's summing the periods from the previous year instead of keeping the periods distinct. I was able to successfully filter by 2 varaibles before, but ther problem is 3+ variables (in this case, account, year, and period).

[v-jiascu-msft · Accepted solution]

Hi @codextero,

Is this what you want?

Method 1 =
VAR lastYear =
    CALCULATE (
        MAX ( fact_value[Year] ),
        FILTER (
            ALLEXCEPT ( 'fact_value', fact_value[Account] ),
            fact_value[Year] < MAX ( fact_value[Year] )
        )
    )
VAR lastPeriod =
    CALCULATE (
        MAX ( fact_value[Period] ),
        FILTER (
            ALLEXCEPT ( 'fact_value', fact_value[Account], fact_value[Year] ),
            fact_value[Period] < MAX ( fact_value[Period] )
        )
    )
RETURN
    CALCULATE (
        SUM ( fact_value[value] ),
        FILTER (
            ALLEXCEPT ( 'fact_value', fact_value[Account] ),
            fact_value[Year] = lastYear
                && fact_value[Period] = MIN ( fact_value[Period] )
        )
    )

Best Regards,

Dale