Dimension with IF, and based on date in filter

[Tadyy]

Hi

I have daily data about quantity on "requirement dates".  Every day I get new data load, that i want to compare between two loads. Comparison is on "requirement dates".

Below is an example of what my final table should look like.  Basically just to sum all dates that are older than the maximum selected date (example: 25.5.)

Current table/pivot:

	23.05.2023	24.05.2023	25.05.2023	26.05.2023	29.05.2023
20. 05. 2023	600	840	840	840	624
25. 05. 2023	-	768	768	600	480

Want table/pivot:

	backlog	25.05.2023	26.05.2023	29.05.2023
20. 05. 2023	600+840	840	840	624
25. 05. 2023	0+768	768	600	480

Is it possible to get the "backlog" column in a pivot table?

I was trying to write an

IF ( [requrement date] < (Date(Aggr(Max([Load Date]),[Load Date]))), "backlog", [requirement date])

into dimension, so i can select the Load Dates i want to compare, but it's not working. Not sure if I can even put write it like this. 

[Kushal_Chawda]

@Tadyy  which Date is in Column and Row?

[Tadyy]

Sorry about that.

[Load date] is in row, [Requirement date] is in column. [Requirement date] is also the one that should be combined into "backlog"

[Kushal_Chawda · Accepted solution]

@Tadyy  try below expression

IF ( [requrement date] < Max(total [Load Date]), "backlog", [requirement date])