Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
Hi gurus
I want to build a consolidated Balance sheet matrix report with YTD figures and months as columns.
Each amount must be converted into local currencies with use of actual currency by the end of each month. I have a fact table, a company table, a calendar table and an exchange rate table .
I have tried this, but I dont seem to get it to work:
YTD with exchangerate EOmonth =
CALCULATE (
SUMX (
'Fact table';
'Fact table'[Amount] * CALCULATE
( sumx(EXCHRATE;
FILTER ( EXCHRATE;
EXCHRATE[Currency] = 'Fact table'[Currency];
EXCHRATE[Date]=SELECTEDVALUE('Calendar'[Date] )
);
) );
DATESYTD ( 'Calendar'[Date] )))
I hope you can guide me.
Solved! Go to Solution.
Create a new column in your fact table
New column in fact table
conv rate = maxx(filter('EXCHRATE','Fact Table'[currency] = 'EXCHRATE'[currency] && 'EXCHRATE'[rate] = 'Fact Table'[rate] ),'EXCHRATE'[conversion])
//New column
Conv Amount = 'Fact Table'[conv rate]* 'Fact Table'[Amount]
Now you can use datesytd to calculate the rates
Appreciate your Kudos. In case, this is the solution you are looking for, mark it as the Solution. In case it does not help, please provide additional information and mark me with @
Thanks. My Recent Blog -
Winner-Topper-on-Map-How-to-Color-States-on-a-Map-with-Winners , HR-Analytics-Active-Employee-Hire-and-Termination-trend
Power-BI-Working-with-Non-Standard-Time-Periods And Comparing-Data-Across-Date-Ranges
Connect on Linkedin
Create a new column in your fact table
New column in fact table
conv rate = maxx(filter('EXCHRATE','Fact Table'[currency] = 'EXCHRATE'[currency] && 'EXCHRATE'[rate] = 'Fact Table'[rate] ),'EXCHRATE'[conversion])
//New column
Conv Amount = 'Fact Table'[conv rate]* 'Fact Table'[Amount]
Now you can use datesytd to calculate the rates
Appreciate your Kudos. In case, this is the solution you are looking for, mark it as the Solution. In case it does not help, please provide additional information and mark me with @
Thanks. My Recent Blog -
Winner-Topper-on-Map-How-to-Color-States-on-a-Map-with-Winners , HR-Analytics-Active-Employee-Hire-and-Termination-trend
Power-BI-Working-with-Non-Standard-Time-Periods And Comparing-Data-Across-Date-Ranges
Connect on Linkedin
Thanks for your reply.
Your proposal brings me the first part of the road, but unfortunately I am not there yet. I want the month of eg. april to be converted to the exchange rate of April and May to be converted to the rate for May and so on.
I have created a file (excel + powerbi that explains my problem, which I hope you can look into
https://1drv.ms/u/s!AvAYB6UZVhl7tFN4U-g0TZDC1UWL?e=gZNPAP
It was a bit tricky. But this should work
What it is =
sumX( SUMMARIZE(CALCULATETABLE('Fact table',DATESYTD('Calendar'[Date])),'Fact table'[Currency],"_MM",max('Calendar'[MMM-yy]),"_DD",CALCULATE(max('Calendar'[Date]),DATESYTD('Calendar'[Date])),"_curr",max('Fact table'[Currency]),"_amt",[amount in LOC]),[_amt]*MAXX(filter('EXCHRATE','EXCHRATE'[Date]=[_DD] && EXCHRATE[Currency]=[_curr]),EXCHRATE[Conversion]))
Hope it works for you
The important thing was to use datesytd in calculate table and not to use month-year grouping in summarize
link https://www.dropbox.com/s/08c2jkn7t8zdo3n/exchangerate%20end%20of%20month.pbix?dl=0
The first thing I see is that you could set up relationships between currency and date in the fact table and the same fields in the exchange rate table. Then your multiplication inside the calculate would be just Fact Table[Amount] * exchrate[Conversion]
Help when you know. Ask when you don't!
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
117 | |
104 | |
77 | |
73 | |
52 |
User | Count |
---|---|
145 | |
109 | |
109 | |
90 | |
64 |