Forum Discussion

karun_r's avatar
karun_r
Microsoft Employee
5 years ago
Solved

Tracking historical values with snowflake pattern

Hi,

 

I am doing a POC where I am trying to track historical values using snowflake pattern. Scenario is something like below

 

I'll have a customer who buys something thru an Account Manager. Customer can switch between Account Managers. What I am interested to see is, to have the customer name, all revenue from that customer thru all current/past acccount managers and the name of the current manager (it could repeat across all rows). 

 

Attached the sample pbix file. (link below).  I am trying to follow a pattern that is mentioned in this book "Agile Data Warehouse Design" by Lawrence Corr. I was not sure if the pattern mentioned in the book was achievable so thought of checking it myself and it seems like I am stuck in implementing this properly

 

 

https://1drv.ms/u/s!AmE9ILAWJzWtgoYjoJGmYRTePU_0Nw?e=E2pV3K

 

 

Need Help
  • Anonymous's avatar
    Anonymous
    5 years ago

    Hi karun_r ,

     

    According to my understanding, you want to get all the name of all managers and the lastest manager of each customer,right?

    You could use the following formula:

    Manager Name =
LOOKUPVALUE (
    AccountManager[Name],
    'AccountManager'[AccountManagerID], [AccountManagerID]
)

     

    All Managers =
CONCATENATEX (
    FILTER ( 'Fact', 'Fact'[CustomerID] = EARLIER ( 'Fact'[CustomerID] ) ),
    [Manager Name],
    ",",
    [Date], ASC
)

     

    The Last/Current Manager =
CALCULATE (
    MAX ( 'Fact'[Manager Name] ),
    FILTER (
        'Fact',
        'Fact'[CustomerID] = EARLIER ( 'Fact'[CustomerID] )
            && 'Fact'[Date]
                = CALCULATE ( MAX ( 'Fact'[Date] ), ALLEXCEPT ( 'Fact', 'Fact'[CustomerID] ) )
    )
)

    The final table output is shown below:

     

    Please take a look at the pbix file here.

     

    Best Regards,
    Eyelyn Qin
    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

2 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    5 years ago

    Hi karun_r ,

     

    According to my understanding, you want to get all the name of all managers and the lastest manager of each customer,right?

    You could use the following formula:

    Manager Name =
LOOKUPVALUE (
    AccountManager[Name],
    'AccountManager'[AccountManagerID], [AccountManagerID]
)

     

    All Managers =
CONCATENATEX (
    FILTER ( 'Fact', 'Fact'[CustomerID] = EARLIER ( 'Fact'[CustomerID] ) ),
    [Manager Name],
    ",",
    [Date], ASC
)

     

    The Last/Current Manager =
CALCULATE (
    MAX ( 'Fact'[Manager Name] ),
    FILTER (
        'Fact',
        'Fact'[CustomerID] = EARLIER ( 'Fact'[CustomerID] )
            && 'Fact'[Date]
                = CALCULATE ( MAX ( 'Fact'[Date] ), ALLEXCEPT ( 'Fact', 'Fact'[CustomerID] ) )
    )
)

    The final table output is shown below:

     

    Please take a look at the pbix file here.

     

    Best Regards,
    Eyelyn Qin
    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.