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
Icon for Super User rankSuper 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