Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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?
Solved! Go to 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
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
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).
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
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
114 | |
97 | |
86 | |
70 | |
62 |
User | Count |
---|---|
151 | |
120 | |
103 | |
87 | |
68 |