cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
karun_r
Microsoft
Microsoft

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

 

karun_r_0-1609980348501.png

karun_r_1-1609980361615.png

 

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

 

 

1 ACCEPTED SOLUTION
Eyelyn9
Community Support
Community Support

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:

1.11.1.1.PNG

 

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.

View solution in original post

2 REPLIES 2
Eyelyn9
Community Support
Community Support

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:

1.11.1.1.PNG

 

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.

View solution in original post

amitchandak
Super User
Super User

@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

Did I answer your question? Mark my post as a solution! Appreciate your Kudos!! !!
Dashboard of My Blogs !! Connect on Linkedin

Learn Power BI Beginners !! Advance Power BI Concepts !! Power BI For Tableau User !! Learn Power BI in Hindi !!
Proud to be a Super User!

Helpful resources

Announcements
UG GA Amplification 768x460.png

Launching new user group features

Learn how to create your own user groups today!

November Power BI Update 768x460.png

Check it Out!

Click here to read more about the November 2021 Updates!

M365 768x460.jpg

Microsoft 365 Collaboration Conference | December 7–9, 2021

Join us, in-person, December 7–9 in Las Vegas, for the largest gathering of the Microsoft community in the world.