YTD amount multiplied with latest rate & multiple currencie

[MikaelSig]

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. 

[amitchandak · Accepted 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

[MikaelSig]

@amitchandak 

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 

[amitchandak]

@MikaelSig 

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

[kentyler]

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]