How do I create a constant line value across a date dimension from one date point?

Question

I have a fact table joined by a Date Dimension and a Product dimension. Within the fact table, I have a date column that specifies when the product size expired and another column that specifies the product size. Ultimately I need to show the product size over time, but the logic should be such that the empty dates are currently the active product size dimension, they should be a constant line until the last date that appears (if any). If necessary, I can also populate the blanks with the present day's date. The problem here being I also need to simultaneously show products that don't have a previously expired size as a constant line across my date dimension. Every product has it's own size, but I would also like my measure to aggregate the capacity.

So the above fact table would ultimately look like. In the below example the measure does not aggregate them, but ideally, I'd like to create a measure that would do that.

Why do you have 4 rows on your table for Product A? should it be just 3? — Joao Leal
Yes, it should just be three rows. there's a typo in the third row making it look like an additional one. — varnhem

Joao Leal Joao Leal · Accepted Answer · 2020-11-13T23:44:21

You can try this:

measure=
   var date = SELECTEDVALUE(dateTable[date])
   var expiryDate = CALCULATE(LASTDATE(table[date]), ALL(table[date]), table[date] >= date)

   Return CALCULATE(SUM(Table[Size]), ALL(table[date]), table[date] = expiryDate)

I wouldn't connect it to the date table, so you don't have to do the All(...) in my example above.

How do I create a constant line value across a date dimension from one date point?

1 Answers