Freezing HIstorical Mapping

[trathman]

I have a well formatted and functioning P&L Power BI report. Our account hierarchy is stored in an excel workbook in a table. We have occasional changes to the hierarchy, e.g. moving an account from SG&A to COGS, etc. When this happens, the historical transactions from previous years also moves. I expected this, however, my leadership would like the historical transactions to remain in the original hierarchy. So if account 1234 was in SG&A->Rent->Insurance in 2024 and is moved to COGS->Rent->Insurance for 2025, they would like 2024 to remain in SG&A->Rent->Insurance. Is there a feasible way of doing this? The hierarchy is pulled over in Rows section of the Visualization panel. Any help would be appreciated.

[danextian]

Hi @trathman 

Power BI retrieves data from the sourceand any updates made to the source will be reflected in the model after a successful refresh. However, if there are changes to dimension attributes that need to be retained over time, these changes must be tracked and stored. This requires maintaining historical records, including when each change occurred and when it ended. Without such tracking, a refresh would overwrite previous values, making it impossible to analyze historical trends or compare past states of the data.

[bhanu_gautam]

@trathman Create DAX measures to dynamically select the correct hierarchy version

Create a table in Excel or directly in Power BI with columns like:

AccountID
HierarchyLevel1
HierarchyLevel2
HierarchyLevel3
EffectiveStartDate
EffectiveEndDate

Import this table into Power BI.

In the Power BI data model, create a relationship between your transaction data and the versioned hierarchy table based on the AccountID. Additionally, use the transaction date to filter the hierarchy version.

DAX
SelectedHierarchy =
VAR TransactionDate = SELECTEDVALUE('Transactions'[TransactionDate])
RETURN
CALCULATE(
MAX('Hierarchy'[HierarchyLevel1]),
FILTER(
'Hierarchy',
'Hierarchy'[AccountID] = SELECTEDVALUE('Transactions'[AccountID]) &&
'Hierarchy'[EffectiveStartDate] <= TransactionDate &&
'Hierarchy'[EffectiveEndDate] >= TransactionDate
)
)

Use these DAX measures in your report to display the correct hierarchy based on the transaction date.

[trathman]

Thank you for the above. I already have a Many:1 relationship between GLJournal and DIM_Account based on the account number. I am a little confused on how to apply "SelectedHierarchy" in Power BI. I am currently using the Matrix visual with month running across the top:

My hierarchy is structured as:

Would the "SelectedHierarchy" be a piece of my "Total Amount Balance USD Actual" measure?

Total Amount Balance USD Actual =
VAR CalcType = SELECTEDVALUE(DIM_Header[CalcType])

VAR DisplayDetailCode = SELECTEDVALUE(DIM_Header[Detail])

VAR isSubHeaderVisible = ISFILTERED(DIM_Account[Account Group 3])

VAR Result = SWITCH(TRUE(),

            isSubHeaderVisible=TRUE() && DisplayDetailCode = 0,BLANK(),

            CalcType=1 ,[Magic Additive Balance USD Actual],

            CalcType=2 ,[Running Total Balance USD Actual],

            CalcType=3 ,FORMAT([% of Running Total Actual], "0.0%"),

            CalcType=4 , CALCULATE([Magic Additive Balance USD Actual], FILTER('GLJrnDtl',  'GLJrnDtl'[JEDate]=MAX('GLJrnDtl'[JEDate]))),

            CalcType=5, FORMAT( [% of Additive Total Actual], "0.0%")

            )

    RETURN Result