Forum Discussion
Merge two dataset on key and date with missing common dates
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)
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
vResultRegards,
Pat
2 Replies
- mahoneypat
Microsoft Employee
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
vResultRegards,
Pat
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