Forum Discussion
Accumulate target based on store opening date
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!
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
2 Replies
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
