cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Sumsar
Helper I
Helper I

Calendar table with dynamic FY columns

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?

5 REPLIES 5
Sumsar
Helper I
Helper I

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
Community Support
Community Support

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
Community Support
Community Support

Hi @Sumsar ,
 
You could create a hierarchy path based on the user permission level.
 
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
Helper I
Helper I

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
Resident Rockstar
Resident Rockstar

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_SecurityGroupsTable dim_SecurityGroups

image.png

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! 🙂





Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




Helpful resources

Announcements
UG GA Amplification 768x460.png

Launching new user group features

Learn how to create your own user groups today!

November Power BI Update 768x460.png

Check it Out!

Click here to read more about the November 2021 Updates!

M365 768x460.jpg

Microsoft 365 Collaboration Conference | December 7–9, 2021

Join us, in-person, December 7–9 in Las Vegas, for the largest gathering of the Microsoft community in the world.