See historical data

[Anonymous]

Hi 

I am working on a report but I need our help.

Example:

I have Slicer for Salesman to filter my table.

The table is year,salesman, customer, amount

The salesma George has curently 5 accounts

The table has to retrive the sum amount of all accounts that George asigned even when somebody was asigned to the same account in the past.

I create a small table to see the accounts of George when I select George from the Salesman slicer.

I have 5 acounts a,b,c,d,e

What I want is to return a table like below.

Period, Account , Previous Salesmn, amount

Period= year, moth ....(selected slicer)

Account  = a,b,c,d,e

Salesman is the previous Salesmans who had this account in the past before George had the account

Amount is the total amount by Period

The problem is that If I select Salesman George...the table will calculate everything that George has  under his name now.

What i need to see, is the history of account that George has now with the amount made my previous salesperson who work in the past to the same account. The ideea is to see the account history over time, when I select the curent Saleman from the slicer.

Now I can see the account in the table but the amounts are refering just to George if I use the Salesman slicer.

How I do that?

I can do this using SQL but I will like to find a way to do it in DAX.

Any help appreciate.

Thanks

[v-jingzhang]

Hi @Anonymous 

How many tables are there in your model? Are these data from the same table? Is there a column that records the period when a salesman works with an account?

Per my understanding, if you have a table 'Salesmen_Accounts' which has Salesman, Account, Period with this Account. And another table 'Sales' which has Account, Customer, Amount. Then you can create a relationship between both tables on Account columns. When you select a salesman from the first table, the second table will be filtered by Account column rather than Salesman column.

If this doesn't work, then you may need to create a measure to calculate the amount. You need to modify the filter context in the measure. 

If you could provide some sample data and expected output, it would be more helpful. 

Best Regards,
Community Support Team _ Jing
If this post helps, please Accept it as Solution to help other members find it.

[Anonymous · Accepted solution]

Hi. I figure out something similar with your suggestion.

I have just one table who contain the salesman the customers and the values and time periods. I create another table for this purpose similar with the original except that will  be not filter by salesman.

Doing that I have a slicer for salesman that give me the customer list , but the values and fiscal periodes are  not being filtered by the Salesman from the slicer.

Thank you

[edhans]

How to get good help fast. Help us help you.

How To Ask A Technical Question If you Really Want An Answer

How to Get Your Question Answered Quickly - Give us a good and concise explanation
How to provide sample data in the Power BI Forum - Provide data in a table format per the link, or share an Excel/CSV file via OneDrive, Dropbox, etc.. Provide expected output using a screenshot of Excel or other image. Do not provide a screenshot of the source data. I cannot paste an image into Power BI tables.