Tracking historical values with snowflake pattern

[karun_r]

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

[v-eqin-msft · Accepted solution]

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.

[amitchandak]

@karun_r , Yet to check the file. But As in power Bi we prefer star schema, I would merge the Account manager name from the employee table by joining account manager ID with employee ID (Column name may differ)

https://radacad.com/append-vs-merge-in-power-bi-and-power-query

Or copy the name from employee table in DAX using one the three approches - https://www.youtube.com/watch?v=czNHt7UXIe8