Crossjoin with filters and unique date values

[agouveia]

Hello,

I'm looking into creating a report that is able to take snapshots of past data and show how it looked liked at a certain past date. 

I have access to the history of the data but I need to create a crossjoin between my history data and my calendar.

My history table looks like this:

Id                  Date                       Status

1               18/08/2023                To do

1               20/08/2023             In Progress

2               20/08/2023                Testing

2               21/08/2023                 Done

...and what I want it to look like:

Id               Date                       Status

1           18/08/2023                 To do

1           19/08/2023                 To do

1           20/08/2023             In progress

1           21/08/2023             In progress

1           22/08/2023             In progress

2           20/08/2023                Testing

2           21/08/2023                 Done

2           22/08/2023                 Done

Basically, I want to have a row for each day until the current date with the associated state until there's a change to the satus. The last status update would be dragged until the current date. I have reach a partial solution but I'm getting duplicate dates for each Id:

MaxDate = 
CALCULATE(max('Card History'[Date]),filter('Card History', 'Card History'[Id]=EARLIER('Card History'[Id])))

MinDate=
CALCULATE(min('Card History'[Date]),filter('Card History', 'Card History'[Id]=EARLIER('Card History'[Id])))


Latest = 
ADDCOLUMNS (
    FILTER(
    CROSSJOIN(
    SUMMARIZECOLUMNS( 'Card History'[Id], 'Card History'[DateTime],  'Card History'[MinDate], 'Card History'[MaxDate],'Card History'[Name]),DISTINCT('Calendar'[Date] )),

'Calendar'[Date]>= 'Card History'[MinDate] && 'Calendar'[Date] <= 'Card History'[MaxDate]),


 "@Qty",
    VAR maxtime =
        CALCULATE ( MAX ( 'Card History'[DateTime] ))
    RETURN
        CALCULATE (DISTINCTCOUNT('Card History'[Id]),filter('Card History', 'Card History'[DateTime] = maxtime && 'Card History'[Index]='Card History'[Id] )
))

Any help would be appreciated!

Thanks!

[johnt75 · Accepted solution]

You can use

New table =
GENERATE(
	VALUES( 'Calendar'[Date] ),
	SELECTCOLUMNS(
		INDEX(
			1,
			FILTER(
				ALL( 'Card History' ),
				'Card History'[Date] <= 'Calendar'[Date]
			),
			ORDERBY( 'Card History'[Date], DESC ),
			PARTITIONBY( 'Card History'[Id] ),
			MATCHBY( 'Card History'[Id], 'Card History'[Date] )
		),
		"Id", 'Card History'[Id],
		"Status", 'Card History'[Status]
	)
)

[agouveia]

Hi @johnt75 ,

This could work but it doesn't add all the dates between the min date and max date of each Id. Anyway I could add those dates in between each status?

Best

[johnt75]

It should work fine as long as you have all the dates in your calendar table. I ran a test using your sample data and it came back with the results you were expecting.

[agouveia]

Yes, correct. I had a relationship to my calendar and that wasn't working but it's fine without it now. Thanks.