Calendar table with dynamic FY columns

[Sumsar]

Hi,

I am creating a solution where multiple customer databases are sourced into Power BI. RLS makes sure that when any of the customers log in, only their own data is visible to them.

However, I have a standard Calendar table related to the fact tables of the model, and I need the Fiscal Month and Fiscal Year columns to be dynamic, depending on which customer logs on. To complicate things, each customer can consist of several companies, which in some occasions may have varying Fiscal Calendars. In this case the Customer will be able to select a company in a slicer, and the Fiscal columns of the Calendar tables should shift according to this. Is this even possible, as calculated columns are only updated at load?

How should I go about this?

[Sumsar]

Yes and no. I found a workaround relying on a table with fiscal periods per customer in my source, and the case changed slightly, so that Fiscal periods varies only on a customer level, making it somewhat more simple.

I can accept your solution though, but I did not have time to test it?

[V-lianl-msft]

Hi @Sumsar ,

Is this problem sloved?
If not, please let me know.

Best Regards,
Liang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[V-lianl-msft]

Hi @Sumsar ,

You could create a hierarchy path based on the user permission level.

Please refer to this thread:https://community.powerbi.com/t5/Desktop/How-to-implement-RLS-for-company-hierarchy/td-p/388345 

Best Regards,
Liang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Sumsar]

Thanks for the input 🙂, I thought about something like this, but the solution will dynamically add new customers, and potentially the model will contain upwards of 100s of them...

[JarroVGIT]

Well this is a complicated solution and it is very labour intensive and a horror to maintain but I can't of any other way to achieve what you need. This is actually drawns from a blogpost I am drafting right now on how to get the current security group of the user in DAX.

Create a dimtable with all security groups in it and add rules for every group to this table.

Table dim_SecurityGroups

Now, you can create different datetables with inactive relations to your facttable and use SWITCH() command to determine what relationship needs to be activated based on if [Groups] contains GroupA for example (it wouldn't if the current user was in GroupB), 

Again, this is very cumbersome and I wouldn't recommend it but it was the only way I could think of right now 🙂

Kind regards

Djerro123

-------------------------------

If this answered your question, please mark it as the Solution. This also helps others to find what they are looking for.

Keep those thumbs up coming! 🙂