Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
Hello,
I am hoping someone can assist me figure out how to complete the report I am developing. The report is an Aging Report that needs to show Multi Currency from a table as of the exact date chosen by the end user and put that exchange rate into the summary for the customer record.
I have two Datasets:
BSA00-MC - This is a Multi Currency table that has Curency ID, Exchange Rate, and Exchange Date
BSA00-RMMC - This is a dataset pulling in 89k individual records from our Accounts receivable module. It includes every single invoice and every single payment for each customer.
I have successfully made the report so that if an end user chooses a date, they can go back in time and see the correct balances of those customer records as of the date they choose.
What I need to do, is on all of those Open balances , be able to match the currency ID , with the Exchange rate as of the specific date the user chooses. However The exchange Rate SUMS up and gives the wrong exchange rate.
What I need is a Vlookup() function to look at the current Currency ID, look into the BSA00-MC table and extract the exchange rate for that Particular currency ID.
I have two filters on the page level of the report.
First Filter - Posting Date from BSA00-RMMC is <= The date I put in the filter
Second Filter - Exchange Date from BSA00-MC = The exact date I enter in the filter.
The Problem is I can not create a relationship in SQL as the Currency rate is determined by the date the users chooses when running the report. So I need to Bring Both datasets into Power BI.
IF I relate the Records on Currency ID, then the balances are all wrong becouse the filter on MultiCurrency limits the recordset.
I only need the Exchange Rate on the TOP table where I have filtered out all 0 balances so that the Exchange rate is calculated JUST on the open balances as seen in the TOP table.
Can this even be accomplished in Power BI? It Works well in SSRS becouse I can use a parameter when loading the dataset and also filter out all Exchange Rates based on the report date.
Solved! Go to Solution.
Using your data without changes
Create this measure
EXCHRATETOUSE = VAR EXCHDATETOUSE = MIN ( 'BSA00-MC'[EXCHDATE] ) RETURN IF ( HASONEVALUE ( 'BSA00-RMMC'[CUSTNMBR] ), LOOKUPVALUE ( 'BSA00-MC'[XCHGRATE], 'BSA00-MC'[CURNCYID], VALUES ( 'BSA00-RMMC'[CCURNCYID] ), 'BSA00-MC'[EXCHDATE], EXCHDATETOUSE ) )
I am also unable to create a relationship becouse Power BI does NOT allow a Many to Many relationship
hi @lcasey
Try with this and modified accord to your needs
ExchangeRatetoUse = LOOKUPVALUE ( 'BSA00-MC'[Exchange Rate], 'BSA00-MC'[CurrencyID], VALUES ( 'BSA00-RMMC'[CurrencyID] ), 'BSA00-MC'[Exchange Date], VALUES ( 'BSA00-MC'[Exchange Date] ) )
Thank you for the reply.
Unfortunatly it did ot work. I am thinking that Power BI just cant handle filtering data based on report date.
For Testing: Try Disabling the Total of your table i believe that the error message dissapear.
One more thing: ¿Do you have duplicates combination of currencyId and Dates?
So there are duplicates.
The BSA00-RMCC table contains 89k records of wich some records have a Curency ID and Others Dont.
BSA00-MC table contains 700K records and includes the exchange rates for every single day , for every single currency id.
I have also tried creating a single table with unique Currency ID's
Here is a Short Video:
http://screencast.com/t/Q04fO9qsmGSF
The Problem is that top Table MUST remain at 637 records and never change. I just need that top table to show the correct Echange Rate AS of The Report date the end user chooses.
Anything I have tried will Increase the record count on that top table.
The Problem is that the
If you deleted the column Count of your table remain the 3K Rows?
Can you share your PBIX file changing sensitive-data. This would be more fast to try to replicate your scenario.
Hello,
Sure, It may take some tim for me to get a clean file to you. I really appreciate you looking at this.
The Ultimate Goal is to get the correct exchange rate to show up in the Exchange rate on the top table:
The Top Table is just filtered so no 0 Balance will show up. Only Customers with open balances should show up on the top table.
If I filter the Multi Currency Table, I get the correct exchange rates. But that Filter is not honored for any formulas.
Ill do a clean up of the Data so no sensitive data is there and upload
Thanks
Using your data without changes
Create this measure
EXCHRATETOUSE = VAR EXCHDATETOUSE = MIN ( 'BSA00-MC'[EXCHDATE] ) RETURN IF ( HASONEVALUE ( 'BSA00-RMMC'[CUSTNMBR] ), LOOKUPVALUE ( 'BSA00-MC'[XCHGRATE], 'BSA00-MC'[CURNCYID], VALUES ( 'BSA00-RMMC'[CCURNCYID] ), 'BSA00-MC'[EXCHDATE], EXCHDATETOUSE ) )
You are a Amazing!
That absolutely worked exactly as I needed. I am speechless and thank you very much!!!
Thank you for the reply.
Unfortunatly it did ot work. I am thinking that Power BI just cant handle filtering data based on report date.
may you can use VALUES function
check this post out
https://msdn.microsoft.com/en-us/library/ee634547.aspx
User | Count |
---|---|
125 | |
108 | |
99 | |
63 | |
62 |
User | Count |
---|---|
135 | |
116 | |
101 | |
71 | |
61 |