Forum Discussion

agouveia's avatar
agouveia
Frequent Visitor
3 years ago
Solved

Crossjoin with filters and unique date values

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...
Need Help
  • johnt75's avatar
    johnt75
    3 years ago

    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]
	)
)
johnt75's avatar
johnt75
Icon for Super User rankSuper User
3 years ago

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's avatar
agouveia
Frequent Visitor
3 years ago

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's avatar
    johnt75
    Icon for Super User rankSuper User
    3 years ago

    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's avatar
      agouveia
      Frequent Visitor
      3 years ago

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