I have data at the lowest level of business and product by month, all the totals are calculated in the reports using hierarchies from these base levels.
I have a hierarchy for the business, mainly geographical, and a hierarchy for the products.
The hierarchies for this year’s reporting are different to the hierarchies used for last year’s reporting. So my prior year number is currently being calculated on the latest hierarchy, However, I need to be able to see it at the hierarchy it was reported at last year.
For example, country A in 2020 had business 1 and 2 and 3. In 2021 it only has business 1 and 2. For my report I am looking at the total of Country A. I am using the latest hierarchy in my reporting but my PY numbers need to be the country A total including business 3.
How can I report on the latest hierarchy, but show the country level totals as per the correct hierarchy for their year?
How many child parents do you have roughly? Could you bin the old hierarchy or better yet couldn’t you have both hierchies in your dimension table and use each one as needed?
Hi @helen.b.jones, I noticed you didn’t provide a enough information for the users to help you with your inquiry.
Providing all information will help users and experts find a solution to your inquiry faster and better.
A perfect initial question includes all of the following:
A clear explanation of the problem you are experiencing
A mockup of the results you want to achieve
Your current work-in-progress PBIX file
Your underlying data file (to allow us to go into Power Query if necessary to transform your data and/or data model – often DAX questions really end up being data modeling solutions)