DAX - FX Rate - Use previous month value until current month gets closed

[Anonymous]

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.

[Anonymous · Accepted solution]

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:

https://1drv.ms/u/s!Amqd8ArUSwDSzz8NKI8SdFZuxoc5

[Anonymous]

Thank you Sir !

Works like a charm !

[Zubair_Muhammad]

@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] )
)