Accumulate target based on store opening date

[ausphil]

I have a stacked column chart that shows, "daily sales amount by store over time". I want a target line that jumps up by a constant value every time a new store is opened (red solid line). See chart below, stores are colour coded, the first time that colour appears is the opening date of that store, and I need a measure to accumulate a constant value when that store is opened. I attempted (dotted red line) to come up with something, closest I could was this:

Target_sales_cumulative =
CALCULATE(
    DISTINCTCOUNT(Table[Store]),
        FILTER(Table, Table[Date] >= [Earliest Date min per Store]
        )
)*[Target per Store]

[Earliest Date min per Store] =

Earliest Date min per Store =
MINX(
 KEEPFILTERS(VALUES('Table'[Store])),
 CALCULATE(MIN('Table'[Date]))
)

[Target per Store] = 240

But it will only jump up if there was sales for that particular date for that store.

Very new to PowerBI so appreciate help for this basic question!

[OwenAuger · Accepted solution]

Hi @ausphil

I would recommend you write Target_sales_cumulative like this:

Target_sales_cumulative =
VAR MaxDate =
    MAX ( 'Table'[Date] )
RETURN
    CALCULATE ( DISTINCTCOUNT ( 'Table'[Store] ), 'Table'[Date] <= MaxDate )
        * [Target per Store]

This measure is similar to what you had already tried: it counts the number of stores that appear up to the max date filtered.

You don't need the Earliest Date min per store measure.

As a side point, I would recommend a Calendar table separate from your sales table, but you can get by without it.

Regards,

Owen

[ausphil]

Thanks so much @OwenAuger! That worked perfectly