Hello guys,
I have a problem that I'm going round in circles trying to solve but can't.
I have a list of clients (c. 500k rows) which is refreshed monthly (at the end of month) - some clients switch to pay from one currency to another, i.e. INV_CURRENCY column ABC client paid on Jan-2015 in USD but on Feb-2015 switched to CAD. I use LOOKUP function for CLIENT_NUMBER column in the "REVENUE" dataset to determine the invoice currency for a specific client and period and thus calculate the revenue (Revenue file, all values are in USD).
I stuck as every month the list of clients is changing and if I simply replace the data with the latest version, it will affect all periods which I don't want to. I'd like to keep a list of clients with monthly data (just add them with new month data ) so the report will show exact data for each month .
I've tried many diffferent solutions like i.e. Inremental Refresh (Datasets + dataflows) or combine CLIENT_NUMBER with DATA REFRESHED columns to have unique client numbers but Power BI doesn't allow to link DATA REFRESHED column (from list of client file) with Calendar (relation is Many to Many) so tables are not showing correct values for periods.
I'm wondering if there is any solutions for this? Would be so greatful to everyone who can help.
Revenue:
| DATE | COMPANY CODE | CLIENT_NUMBER | AMOUNT in USD |
| 1/1/2015 | YT11 | 111111 | 672.84 |
| 1/2/2015 | YT11 | 222222 | 103.05 |
| 1/4/2015 | YT11 | 333333 | 0.09 |
| 1/2/2015 | PL12 | 444555 | 0.06 |
| 1/2/2015 | PL12 | 777888 | 18.64 |
| 1/2/2015 | PL12 | 999000 | 84.10 |
| 1/3/2015 | PL12 | 999000 | 46.33 |
| 1/4/2015 | PL12 | 999000 | 0.01 |
| 1/5/2015 | PL12 | 999000 | 72.23 |
| 1/6/2015 | PL12 | 444555 | 9.67 |
| 1/7/2015 | PL12 | 444555 | 7.01 |
| 1/8/2015 | YT11 | 111111 | 6.02 |
| 1/8/2015 | YT11 | 111111 | 7.59 |
| 1/8/2015 | YT11 | 111111 | 8.26 |
List of clients:
| CLIENT_NUMBER | CLIENT_NAME | INV_CURRENCY | JOINED | DATA REFRESHED |
| 111111 | ABC | USD | 1/1/1999 | Jan-15 |
| 222222 | XYZ | CAD | 1/1/2015 | Jan-15 |
| 333333 | DFE | EUR | 1/1/2005 | Jan-15 |
| 444555 | CYY | CAD | 1/5/2004 | Jan-15 |
| 777888 | TYY | CAD | 1/12/2005 | Jan-15 |
| 999000 | XZAA | EUR | 1/11/2005 | Jan-15 |
| 111111 | ABC | CAD (changed currency) | 1/1/1999 | Feb-15 |
| 222222 | XYZ | CAD | 1/1/2015 | Feb-15 |
| 333333 | DFE | NOK (changed currency) | 1/1/2005 | Feb-15 |
| 444555 | CYY | USD | 1/5/2004 | Feb-15 |
| 777888 | TYY | CAD | 1/12/2005 | Feb-15 |
| 999000 | XZAA | EUR | 1/11/2005 | Feb-15 |
