Merge two dataset on key and date with missing common dates

[Anonymous]

Hello,

I have two dataset with a common key and dates,the second dataset has a cumulative value over the date for each key.

My first dataset is :

Key	Date
key1	01/01/2019
key1	03/01/2019
key1	09/01/2019
key2	03/01/2019
key2	05/01/2019
key3	06/01/2019

My second dataset :

Key	Date	Cumulative value
key1	01/01/2019	1
key1	06/01/2019	3
key2	01/01/2018	15
key2	08/01/2018	25
key3	05/01/2019	15

I want to add the Cumulative value of my second dataset in my first but: my second dataset don't have all dates listed in the first dataset.

If a date is missing in the first dataset the value should be the one we find at the earliest date for the same key in the second dataset.

The expected result for this should be :

Key	Date	Cumulative value
key1	01/01/2019	1
key1	03/01/2019	1
key1	09/01/2019	3
key2	03/01/2019	25
key2	05/01/2019	25
key3	06/01/2019	15

Any idea how to proceed ?

(to note: dates in first dataset will only be more recent than the second dataset, so we won't be looking for a value from a previous date in the second dataset)

[mahoneypat · Accepted solution]

Here is a calculated column expression you can try on your first table to get your desired result.  It assumes there is no relationship between the two table

Cumul Value =
VAR vThisDate = T1[Date]
VAR vThisKey = T1[Key]
VAR vResult =
    CALCULATE (
        LASTNONBLANKVALUE (
            T2[Date],
            MAX ( T2[Cumulative Value] )
        ),
        T2[Key] = vThisKey,
        T2[Date] <= vThisDate
    )
RETURN
    vResult

Regards,

Pat

[Anonymous]

Hello @mahoneypat ,

Thank you for the calculated column expression, when I add it I have the following error message:

DAX comparison operations do not support comparing values of type Text with values of type Date. Consider using the VALUE or FORMAT function to convert one of the values.

I checked the date column of my two tables, the data type is date for both and I formated them in the same fashion ("03/14/2001 (mm/dd/yyyy)"), should I check something else ?

EDIT:

My bad, T2[Cumulative Value] was not defiend as Decimal type.

It is done now but the result is "1" for all the rows in the new column.

My key column (categorical values) is in text format, could it be the reason ?

EDIT2:
I was missing a bracket, working perfectly! Thank you @mahoneypat