Fetch previous date value if an entry is missing for a said date.

[Ani26]

Hi Team,

Hope you all are doing good!!

I have a requirement that I need to show exchange rates for all currencies in front of the dates in a calendar table where the day is either Friday or a month end. 

Let me explain it to you with sample data and expected output.

Table1 : Rate

FromCurrency	EffectiveDate	ExchangeRate
AED	3/15/2020	0.27225
AED	3/16/2020	0.27223
AED	3/17/2020	0.27225
AED	3/18/2020	0.27225
AED	3/19/2020	0.27223
ZTWD	3/15/2020	0.03314
ZTWD	3/16/2020	0.03315
ZTWD	3/17/2020	0.03309
ZTWD	3/18/2020	0.03315
ZTWD	3/19/2020	0.03314
ZTWD	3/20/2020	0.03314

Table2 : Calendar

Date	WeekNumber	WeekFillup	FlagInfo
3/15/2020 0:00	12	03/13/2020	0
3/16/2020 0:00	12	03/20/2020	0
3/17/2020 0:00	12	03/20/2020	0
3/18/2020 0:00	12	03/20/2020	0
3/19/2020 0:00	12	03/20/2020	0
3/20/2020 0:00	12	03/20/2020	1
3/21/2020 0:00	12	03/20/2020	0

Requirement:

I want to have a table with following columns:

Date

EffectiveDate

FromCurrency

WeekNumber

WeekFillup

FlagInfo

Rate

But the Rate should be the value from the Rate table where WeekFillup = EffectiveDate.

For this I merged the two tables selecting the above columns for joining and I got 50% of the requirement.

Now suppose, due to a holiday in UAE there was no entry in the Rate table for 03/20/2020 which is a Friday with FlagInfo value as 1 (FlagInfo value is a column which has a value 1 when the day is either a Friday or month end day else it is 0).
But in the output table in front of the Date column having value as 03/20/2020 we want the Rate column to have a previous date value (03/19/2020) from the Rate table. This is something where I am stucked. I understand that I may need to use CROSSJOIN function but not able to apply it.

The expected output is as below where the bold values are actually the rate for 03/19/2020 as the record was missing for 20th March for AED but it worked as expected for ZTWD as there was no entry missing for 20th March.

Date	EffectiveDate	FromCurrency	WeekNumber	WeekFillup	FlagInfo	Rate
3/15/2020	3/15/2020	AED	12	3/13/2020	0	0.27225
3/16/2020	3/16/2020	AED	12	3/20/2020	0	0.27223
3/17/2020	3/17/2020	AED	12	3/20/2020	0	0.27223
3/18/2020	3/18/2020	AED	12	3/20/2020	0	0.27223
3/19/2020	3/19/2020	AED	12	3/20/2020	0	0.27223
3/20/2020	3/20/2020	AED	12	3/20/2020	1	0.27223
3/15/2020	3/15/2020	ZTWD	12	3/13/2020	0	0.03314
3/16/2020	3/16/2020	ZTWD	12	3/20/2020	0	0.03314
3/17/2020	3/17/2020	ZTWD	12	3/20/2020	0	0.03314
3/18/2020	3/18/2020	ZTWD	12	3/20/2020	0	0.03314
3/19/2020	3/19/2020	ZTWD	12	3/20/2020	0	0.03314
3/20/2020	3/20/2020	ZTWD	12	3/20/2020	1	0.03314

I hope I was able to explain it properly. If in case any further information required do let me know.

Thanks,

Ani

[v-lid-msft · Accepted solution]

Hi @Ani26 ,

We can create a table to meet your requirement.

Table =
ADDCOLUMNS (
    CROSSJOIN ( DISTINCT ( 'Rate'[FromCurrency] ), 'Calendar' ),
    "EffectiveDate", [Date],
    "Rate",
    VAR result =
        CALCULATE (
            SUM ( 'Rate'[ExchangeRate] ),
            'Rate'[FromCurrency] = EARLIER ( [FromCurrency] ),
            'Rate'[EffectiveDate] = EARLIER ( [Date] )
        )
    RETURN
        IF (
            result = BLANK ()
                && [FlagInfo] = 1,
            VAR lastD =
                CALCULATE (
                    MAX ( 'Rate'[EffectiveDate] ),
                    'Rate'[FromCurrency] = EARLIER ( [FromCurrency] ),
                    'Rate'[EffectiveDate] < EARLIER ( [Date] )
                )
            RETURN
                CALCULATE (
                    SUM ( 'Rate'[ExchangeRate] ),
                    'Rate'[FromCurrency] = EARLIER ( [FromCurrency] ),
                    'Rate'[EffectiveDate] = lastD
                ),
            result
        )
)

BTW, pbix as attached.

Best regards,

[Ani26]

Thank you so much @v-lid-msft . This also worked as expected. Also I tried one more approach and even that fetched me the required output. But I think this is more covenient. Thank you so much again.