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

The ultimate Microsoft Fabric, Power BI, Azure AI & SQL learning event! Join us in Las Vegas from March 26-28, 2024. Use code MSCUST for a $100 discount. Register Now

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
Fabric Community Conference

Microsoft Fabric Community Conference

Join us at our first-ever Microsoft Fabric Community Conference, March 26-28, 2024 in Las Vegas with 100+ sessions by community experts and Microsoft engineering.

February 2024 Update Carousel

Power BI Monthly Update - February 2024

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

Fabric Career Hub

Microsoft Fabric Career Hub

Explore career paths and learn resources in Fabric.

Fabric Partner Community

Microsoft Fabric Partner Community

Engage with the Fabric engineering team, hear of product updates, business opportunities, and resources in the Fabric Partner Community.

Top Solution Authors