Forum Discussion

MarcF's avatar
MarcF
Advocate IV
10 years ago
Solved

Best way to fix missing days for currency conversion API

Hi all,   I am looking for a best practice to fill missing days with my €/£ Quandl currency conversion API. The API feeds in currecny conversion for week days but returns null values during weekend...
Need Help
Show and Tell
  • Eric_Zhang's avatar
    Eric_Zhang
    10 years ago

    MarcF

     

    Supposing the dataset is as below.

     

     

    Check a measure as

     

    Currency = 
VAR lastNoBlankValDate =
    MAXX (
        FILTER (
            ALL ( EURvsGBP ),
            EURvsGBP[DATE] <= MAX ( EURvsGBP[DATE] )
                && EURvsGBP[VALUE] <> BLANK ()
        ),
        EURvsGBP[DATE]
    )
RETURN
    CALCULATE (
        SUM ( EURvsGBP[VALUE] ),
        FILTER ( ALL ( EURvsGBP ), EURvsGBP[DATE] = lastNoBlankValDate )
    )

     

     

     

    Regarding week-based, check if Week-Based Time Intelligence in DAX helps.

Eric_Zhang's avatar
Eric_Zhang
Microsoft Employee
10 years ago

MarcF

 

Supposing the dataset is as below.

 

 

Check a measure as

 

Currency = 
VAR lastNoBlankValDate =
    MAXX (
        FILTER (
            ALL ( EURvsGBP ),
            EURvsGBP[DATE] <= MAX ( EURvsGBP[DATE] )
                && EURvsGBP[VALUE] <> BLANK ()
        ),
        EURvsGBP[DATE]
    )
RETURN
    CALCULATE (
        SUM ( EURvsGBP[VALUE] ),
        FILTER ( ALL ( EURvsGBP ), EURvsGBP[DATE] = lastNoBlankValDate )
    )

 

 

 

Regarding week-based, check if Week-Based Time Intelligence in DAX helps.

anandav's avatar
anandav
Skilled Sharer
8 years ago

Eric_Zhang,

 

How can this be achieved in a calculated column?

 

The measure is working but I am trying to create a calculated column to fill the missing values.

 

Any help will be appreciated.