I need filtering and consolidation of group companies with different year ends (February and March year ends). I am having difficulty with date tables and time analysis when it comes to filtering on individual companies and consolidating the group.
Karen - Mediacloud
So there’s a bit to this one, and could be completed in a few ways.
Probably the best way it to create multiple columns in your date table. So you would have unique FY columns for each group company.
For example; Group1 FY, Group2 FY
To create those financial years you will need to utilise some logic within calculated columns. Then once that’s done create measures which sum up the results.
Instead of having one measure for sales, you’ll likely have to create a few depending on how many groups you have.
Let me know if more information required here. As mentioned there’s a bit to this one.
Hi Sam
Thanks for your advice.
I have created unique FY columns as suggested.
Where I come stuck is the relationship between the company dimension and the FY column. For example, I would like to create a sales bar chart with a month axis, which can be filtered by company and financial year. I have created a slicer for the company dimension.
Please could you expand on how to create a slicer for the financial year dimension which points to the correct FY column?
Many thanks
Karen
Hi Karen,
Would it be possible to place a few images of the model here in the forum post, I’m struggling to get the picture on the model. This is actually quite a unique scenario. Chrs
Also just an outline around the calculations you want to see in the visual as well. That would be great.
Hi Sam
I have posted below 2 simple filterings for the model. 1) Company 2 which has a February year end, and 2) Company 3 which has a March year end.
You will see there are two FY dimensions (as per the your suggestion)
Is it possible to create one dimension for FY? It would work as follows: if Company2 is selected, the FY dimension will know to use Feb year end in measures and visuals. If Company3 is selected, March year end is used in measures and visuals.
I will ultimately also need to consolidate the companies at a summary level.
Many thanks for your assistance.
What I think you want to do here Karen has a few steps.
You will want to create a few measures to ultimately create what you need.
So instead of just having ‘total sales’ I think you’ll want to create for example Company1 Sales, Company2 Sales etc etc.
To do that what you can do is use CALCULATE( [Total Sales], Company = x )
Then you may want to create one measure that has all those new measures inside it. For this you may want to look at SWITCH/ TRUE logic. There’s a few video tutorials I’ve created on this but the same idea it mentioned in this tutorial about dynamic visuals. This way you integrate these new measures into one measure
The next and last thing you might want to do is create a new table that just has
FY14
FY15
etc etc
Then draw relationships from this table down to each column in your date table with the unique financial years for each group. This is how you can get your consolidation ultimately, because you’ll then use the FY dimension in this table and it will filter this new measures concurrently based on the FY you select.
Ok there’s a bit to this, but not the easiest thing to solve in Power BI.
Hopefully this assists and gives you a few ideas, any question reply here. Chrs, Sam
Fantastic thank you Sam! I will definitely give this a try and let you know if I come right.
Hi Sam
Thanks for pointing me in the right direction. I have used the following which accommodates filtering by company and consolidating the companies. Please let me know if you pick up any short comings in this solution.
Sales = SWITCH(TRUE(),
[Company Selected] = "Company1", CALCULATE([Company1 Sales], USERELATIONSHIP('FY Table'[FY],'Date Table'[Group1 FY02])),
[Company Selected] = "Company2", CALCULATE([Company2 Sales], USERELATIONSHIP('FY Table'[FY],'Date Table'[Group1 FY03])),
CALCULATE([Company1 Sales], USERELATIONSHIP('FY Table'[FY],'Date Table'[Group1 FY02]))+
CALCULATE([Company2 Sales], USERELATIONSHIP('FY Table'[FY],'Date Table'[Group2 FY03]))
)Nice, that looks great. Does it return what you needed?
It does yes, thanks again