Forum Discussion

ausphil's avatar
ausphil
Helper I
8 years ago
Solved

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

    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

OwenAuger's avatar
OwenAuger
Super User
8 years ago

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's avatar
    ausphil
    Helper I
    8 years ago

    Thanks so much OwenAuger! That worked perfectly