Forum Discussion

cristianj's avatar
cristianj
Icon for Helper IV rankHelper IV
4 years ago
Solved

Hello. i am tryng to get previously valid data

 

Hello, i am trying to get the exchange rate from official site, but they have data only for working day, so i try to make this column in which if i don,t have a valid date , to get the previously valid date

this is the formula

EurZT = if(ISBLANK('Curs Valutar Zilnic'[Eur])
            ,calculate(LASTNONBLANK('Curs Valutar Zilnic'[Eur],1),filter(all('Curs Valutar Zilnic'),('Curs Valutar Zilnic'[Eur])>0&&'Curs Valutar Zilnic'[DateYearNumber]<[DateYearNumber]))
            ,('Curs Valutar Zilnic'[Eur])
        )
the problem is the the data isblank.
Thank you for your help.

 

Need Help
  • tamerj1's avatar
    tamerj1
    4 years ago

    Hi cristianj 
    Please try

    EurZT =
VAR CurrentEUR = 'Curs Valutar Zilnic'[Eur]
VAR CurrentDate = 'Curs Valutar Zilnic'[Date]
VAR PreviousTable =
    FILTER ( 'Curs Valutar Zilnic', 'Curs Valutar Zilnic'[Date] < CurrentDate )
VAR NonBlankTable =
    FILTER ( PreviousTable, 'Curs Valutar Zilnic'[Eur] <> BLANK () )
VAR PreviousRecord =
    TOPN ( 1, NonBlankTable, 'Curs Valutar Zilnic'[Date] )
VAR PreviousEUR =
    MAXX ( PreviousRecord, 'Curs Valutar Zilnic'[Eur] )
RETURN
    COALESCE ( CurrentEUR, PreviousEUR )

2 Replies

  • tamerj1's avatar
    tamerj1
    Icon for Community Champion rankCommunity Champion
    4 years ago

    Hi cristianj 
    Please try

    EurZT =
VAR CurrentEUR = 'Curs Valutar Zilnic'[Eur]
VAR CurrentDate = 'Curs Valutar Zilnic'[Date]
VAR PreviousTable =
    FILTER ( 'Curs Valutar Zilnic', 'Curs Valutar Zilnic'[Date] < CurrentDate )
VAR NonBlankTable =
    FILTER ( PreviousTable, 'Curs Valutar Zilnic'[Eur] <> BLANK () )
VAR PreviousRecord =
    TOPN ( 1, NonBlankTable, 'Curs Valutar Zilnic'[Date] )
VAR PreviousEUR =
    MAXX ( PreviousRecord, 'Curs Valutar Zilnic'[Eur] )
RETURN
    COALESCE ( CurrentEUR, PreviousEUR )