cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
codextero Frequent Visitor
Frequent Visitor

Look up last year's data by account

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.

 

AccountYearPeriodvalue
1201011
1201021
1201113
1201124
2201015
2201026
2201117
2201128

 

and a dim_period lookup in another table

 

DateYearPeriod
01/01/201020101
02/01/201020102
01/01/201120111
02/01/201020112

 

 

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?

1 ACCEPTED SOLUTION

Accepted Solutions
Community Support Team
Community Support Team

Re: Look up last year's data by account

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

Look_up_last_year_s_data_by_account

 

Best Regards,

Dale

Community Support Team _ Dale
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
3 REPLIES 3
Community Support Team
Community Support Team

Re: Look up last year's data by account

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

Look_up_last

 

 

Best Regards,

Dale

Community Support Team _ Dale
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
codextero Frequent Visitor
Frequent Visitor

Re: Look up last year's data by account

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

Community Support Team
Community Support Team

Re: Look up last year's data by account

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

Look_up_last_year_s_data_by_account

 

Best Regards,

Dale

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