- Forums
- Get Help with Power BI
- Desktop
- Service
- Report Server
- Power Query
- Mobile Apps
- Developer
- DAX Commands and Tips
- Let's Talk Data
- Custom Visuals Development Discussion
- Community Support
- Community Information
- Community Accounts & Registration
- Using the Community
- Community Feedback
- Training and Consulting
- Dashboard in a Day
- EdX Specific Training Discussion Forum

Turn on suggestions

Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.

Showing results for

- Microsoft Power BI Community
- Forums
- Get Help with Power BI
- Desktop
- Re: Look up last year's data by account

Topic Options

- Subscribe to RSS Feed
- Mark Topic as New
- Mark Topic as Read
- Float this Topic for Current User
- Bookmark
- Subscribe
- Printer Friendly Page

codextero

Frequent Visitor

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

06-29-2018
07:40 AM

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.

1 ACCEPTED SOLUTION

Accepted Solutions

v-jiascu-msft

Community Support Team

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

07-05-2018
01:16 AM

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

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.

If this post

3 REPLIES 3

v-jiascu-msft

Community Support Team

Re: Look up last year's data by account

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

07-03-2018
12:27 AM

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

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.

If this post

Highlighted
##

codextero

Frequent Visitor

Re: Look up last year's data by account

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

07-03-2018
10:37 AM

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

Community Support Team

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

07-05-2018
01:16 AM

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

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.

If this post