Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply
ausphil
Helper I
Helper I

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!

 

powerbi q.PNG

1 ACCEPTED SOLUTION
OwenAuger
Super User
Super User

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


Owen Auger
Did I answer your question? Mark my post as a solution!
Blog
Twitter
LinkedIn

View solution in original post

2 REPLIES 2
OwenAuger
Super User
Super User

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


Owen Auger
Did I answer your question? Mark my post as a solution!
Blog
Twitter
LinkedIn

Thanks so much @OwenAuger! That worked perfectly

Helpful resources

Announcements
Fabcon_Europe_Social_Bogo

Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

Power BI Carousel June 2024

Power BI Monthly Update - June 2024

Check out the June 2024 Power BI update to learn about new features.

PBI_Carousel_NL_June

Fabric Community Update - June 2024

Get the latest Fabric updates from Build 2024, key Skills Challenge voucher deadlines, top blogs, forum posts, and product ideas.

RTI Forums Carousel3

New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.