Multiple Financial Years for different Customers

[JateenK]

Hi all

I have researched and spent time trying to find the best solution to this problem but need some assistance getting this over the line as i'm still gaining experiance in PowerBI.

Scenario:

Using row level security, depending on the ClientID, client will only reference their own data upon login.

However certain clients have fin year starting March, other July etc. so have a maximum of 12 variations of FinYears to cater for and need to have this dynamically show in 1 report as opposed to having different report versions for each Fin Year.

I've gotten close to dynamically changing the financial year dependant on the customer but each possible solution has its own complications

I have limited success with 2 approaches:

METHOD 1: Create a financial calendar with 12 versions of a calendar for each calendar year.

ie: 365 days x 12 fin variations

So for 2022, will have 365 days with finyear starting March, then another 365 days with FinYear starting April etc

Each calendar iteration has a FinMonthStartMonth Key indicating the numeric FinMonth (1 to 12) - this appended to the calendar date (yyyymmdd) which determines the unique key for the Financial Calendar table

Each Client then is also assigned a FinMonth Key (1 to 12) which concatenated to the DateTransaction provides the Fact table key to create the relationship

RESULT: The financial calendar works for the current year dynamically changing when the client changes

 

PROBLEM: with this method the calendar table date is no longer unique, the time-intelligence DAX no longer is usable and this is a requirement, most commonly used for:

- Previous Month : 

CALCULATE([Sales Value], DATEADD('Dim Calendar'[CalendarDate], -1, MONTH))

- Prior Year Month: 

CALCULATE([Sales Value], SAMEPERIODLASTYEAR('Dim Calendar'[CalendarDate]))

With this no longer working, i would need to be able to recreate the time intelligence in DAX.

I've almost got the Previous month to work using an Offset but if the FinStart Month is July, it always brings back the Prev month for June - always 1 month too many:

Any assistance on getting the previous month and prior year month to display in the current month would be appreciated as the grid must only show the 12 months of the selected Fin Year.

METHOD 2:  Standard calendar is used - with extra columns for each Financial Year variation - So a maximum of 12 extra FinYear columns would be required to cater for all variations.

RESULT: Time intelligence works as usual so calculations are simple and effective and give the correct data for client's respective FinYear

PROBLEM: with this method, in order to change the Financial year, the FinYear slicer requires the field to be changed manually.

Once changed, it has to be reset to the default year that was selected.

I have tried to create a dynamic column using a Switch that would determine which FinYear column to use depending on the client selected, but this wont work due to calculated columns only calculating upon refreshing of data.

At present with Method 2, i have 3 reports linked to the datasource to cater for 3 different financial years but this means any changes and enhancements to reports require duplication of time and effort on each front end.

Any ideas of workarounds would be appreciated. Method 2 is the preferred method but it appears to be the trickier of the 2 methods

PBIX file for this scenario: MultiFinYear.pbix 

Please let me know if any other pertinent info required.

Thanks

Jateen

[v-easonf-msft]

Hi, @JateenK 

Is it possible to convert the queries to DirectQuery source queries and try dynamic M query Parameter?

Best Regards,
Community Support Team _ Eason

[JateenK]

Hi there

Unfortunately with the limitations of Direct Query impacting the application, Import mode is the apps setup.