Forum Discussion
How to fill the gaps in values between dates
1. Sorry for my English.
2. Here is the question.
I have gaps in dates, because rates set up not every day.
What i need is a measure that give me day to day values of rates.
for example:
Date-Rate
07/03/18 - 26.60
13/03/18 - 26.20
16/03/18 - 26.50
03/04/18 - 26.30
the goal is to have
07/03/18 - 26.60
08/03/18 - 26.60
09/03/18 - 26.60
10/03/18 - 26.60
11/03/18 - 26.60
12/03/18 - 26.60
13/03/18 - 26.20
14/03/18 - 26.20
etc.
I know that this is VERY easy task, but i cant handle it.
3 tables.
2 dimention tables: DimCurrency (PK Guid), Calendar (PK Date)
1 fact table: FactCurrenciesRates (FK DimCurrency.Guid, FK Calendar.Date)
Thank you all in advance.
Anonymous,
Drag [Date] from Calendar table and add measure below.
Measure = VAR d = SELECTEDVALUE ( 'Calendar'[Date] ) RETURN CALCULATE ( LASTNONBLANK ( FactCurrenciesCourses[ExchangeRate], 1 ), FactCurrenciesCourses[Date] <= d, CROSSFILTER ( 'Calendar'[Date], FactCurrenciesCourses[Date], NONE ) )
3 Replies
v-chuncz-msftCommunity Support
Anonymous,
Drag [Date] from Calendar table and add measure below.
Measure = VAR d = SELECTEDVALUE ( 'Calendar'[Date] ) RETURN CALCULATE ( LASTNONBLANK ( FactCurrenciesCourses[ExchangeRate], 1 ), FactCurrenciesCourses[Date] <= d, CROSSFILTER ( 'Calendar'[Date], FactCurrenciesCourses[Date], NONE ) )Check-out these instructions to determine which of the two solutions will work best for you. Both solutions posted here are industry best.