Forum Discussion
Report not capturing all relevant data
Hi all,
Apologies if my question is a bit long winded, it's a little difficult to explain my issue.
I imported 6 tables from sql for my report, each giving different information on accounts activity (invoice details, products purchased, contact details, credit note details...). I'm trying to display revenue year to date by salesperson last year vs this year, and this covers about 2000 individual accounts.
The problem is that some accounts may have been assigned to a different salesperson last year, so when I run the report it is giving me back 'incorrect' information. (I want the value for an account to go towards the salesperson that it is assigned to right now).
From what I can see, the system is looking up the invoice number, and the salesperson that is associated with that. I can't figure out a way to look up the account and which salesperson is currently assigned to it(I don't care who was in charge of the account for a certain invoice, I'm only interested in who is assigned to it now).
Would anybody have any suggestions? Thanks in advance!
I have build SSAS multidimentional models with implemented currency conversion, but I still haven't done this in DAX.
However what you should need is you Currency Exch Rate table where (if data exists in your Dynamics NAV) you should be able to find your exchangerate with a from date. I am guessing you source data have some sort of posting date, so you need some DAX code that will lookup the exchangerate that was current when the transaction was made and then multiply with your amount. If you append this table to you already existing one then you should properly need an exchange rate (= 1 assuming that you current date is EURO) for this data too or your measure will break.
I think you should try to open a new thread with your currency problem and provide a good description of your data then I am sure some of the DAX experts here will be able to provide you with a formula.
9 Replies
You should have add a table with all you accounts to you model and this table should also hold the current salesperson assigned to the account. Then create links from you table with your values to the account table and when you use salesperson from your account table in a visual this will show your values for each current salesperson to the account.
Thanks for your suggestion.
I already have a separate table imported with current salesperson and am using this when building my report. However the report still seems to be looking up the salesperson that is associated with an invoice, and not the one that is currently assigned to an account.
When I created the relationships between the tables I used invoice number as this was the only common trait. Could this be the reason it is now looking up salesperson by invoice?
