Forum Discussion
DAX - FX Rate - Use previous month value until current month gets closed
Hi guys,
Title of the post says it all.
Would it be possible to write a measure in DAX that uses the FX rate of the previous month until the current month gets closed ?
FX rate for the current month is currently NULL in my table and only gets populated until month-end.
In the meantime, we use the rate of the previous month.
Thank in advance for the help.
Jason.
Couple of ways to do this, but here's the DAX version ( well mostly Dax..)
1. Need a calendar table. In the attached PBIX file in Power Query there is a function that will produce the table after you enter in the start and end year.
2. Load that, and mark that as a Date table. Then be sure to relate that to the Currency Table ( 1 to many )
3. From there, use the Dates from the DimCalendar table for your matrix. There's a column I added in that is marked as End of Month so that i only pulls in end of the month ( since now we have every single date in the years)
4. Then these measures:
CurrencyRate = SUM ('Currency'[Rate] )Prev Month = Var __CurrencyRate = SUM ( Currency[Rate]) Return Var __PrevMonth= CALCULATE( [CurrencyRate], PREVIOUSMONTH(DimCalendar[Date])) Return IF( NOT ( ISBLANK( __CurrencyRate)), __CurrencyRate, __PrevMonth )Final outut:
here is the pbix file:
3 Replies
Anonymous
Try this mEASURE
Measure = IF ( ISBLANK ( SELECTEDVALUE ( Table1[Histo Rate] ) ), CALCULATE ( MAX ( Table1[Histo Rate] ), LASTNONBLANK ( ALL ( Table1[Month] ), CALCULATE ( MIN ( Table1[Histo Rate] ) ) ) ), SELECTEDVALUE ( Table1[Histo Rate] ) )Couple of ways to do this, but here's the DAX version ( well mostly Dax..)
1. Need a calendar table. In the attached PBIX file in Power Query there is a function that will produce the table after you enter in the start and end year.
2. Load that, and mark that as a Date table. Then be sure to relate that to the Currency Table ( 1 to many )
3. From there, use the Dates from the DimCalendar table for your matrix. There's a column I added in that is marked as End of Month so that i only pulls in end of the month ( since now we have every single date in the years)
4. Then these measures:
CurrencyRate = SUM ('Currency'[Rate] )Prev Month = Var __CurrencyRate = SUM ( Currency[Rate]) Return Var __PrevMonth= CALCULATE( [CurrencyRate], PREVIOUSMONTH(DimCalendar[Date])) Return IF( NOT ( ISBLANK( __CurrencyRate)), __CurrencyRate, __PrevMonth )Final outut:
here is the pbix file:
