Forum Discussion

trathman's avatar
trathman
Regular Visitor
1 year ago

Freezing HIstorical Mapping

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 ...
Need Help
bhanu_gautam's avatar
bhanu_gautam
Super User
1 year ago

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's avatar
trathman
Regular Visitor
1 year ago

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