Fill in gaps in exchange rate table

[Yggdrasill]

Hi !

Using only one table - What is wrong with this calculated column ?

Ex.Rate_cont = 
IF (
    dimDate[Ex.Rate] = BLANK (),
    CALCULATE (
        FIRSTNONBLANK ( dimDate[Ex.Rate], dimDate[Ex.Rate] ) ,
        FILTER ( dimDate, dimDate[Date] <= EARLIER ( dimDate[Date] ) )
    ),
    dimDate[Ex.Rate]
)

See the results below
Trying to fill the gaps with the last registered exchange rate

[Anonymous · Accepted solution]

@Yggdrasill,

Please use Dax below instead.

New Value = 
VAR LastNonBlankDate =
    CALCULATE (
        LASTNONBLANK ( dimDate[Date], 1 ),
        FILTER (
            ALL ( dimDate ),
            dimDate[Date]<= EARLIER (  dimDate[Date] )
                && NOT ( ISBLANK ( dimDate[Ex.Rate]) )
        )
    )
RETURN
    CALCULATE (
        SUM ( dimDate[Ex.Rate] ),
        FILTER ( ALL ( dimDate ), dimDate[Date] = LastNonBlankDate )
    )

For more details, please check attached PBIX file.

Regards,
Lydia

[Yggdrasill]

Thank you Lydia ! Still wondering what was wrong with my previous formula though ! 

[Ashish_Mathur]

Hi,

Does this calculated column formula work?

Ex.Rate_cont = IF(dimDate[Ex.Rate]=BLANK(),LOOKUPVALUE(dimDate[Ex.Rate],dimDate[Date],CALCULATE(MAX(dimDate[Date]),FILTER(dimDate, dimDate[Date]<=EARLIER( dimDate[Date])))),dimDate[Ex.Rate])

[Yggdrasill]

Hi @Ashish_Mathur
No, it returns the same result as the original column with blanks on weekends and holidays.

[Ashish_Mathur]

Hi,

Share the link from where i can download your PBI file.

[Yggdrasill]

Click here

does this work ?