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
jasminwing
Frequent Visitor

Calculate new monthly target based on number of months remaining in year

 

I want to calculate a new monthly sales target based on the sales achieved year to date vs the annual target, divided by the number of months left in the year.

 

My targets are in a separate table to my sales.

Targets table name: Targetsales

Sales table name: Orders

 

I have successfully created a measure for the Year To Date Target by using the following formula to multiple the monthly sales target by the number of months passed (based on today's date):

SALES TARGET YTD = Targetsales[TARGET_VALUE_MONTHLY] * MONTH(TODAY())

 

I have also created a measure to calculate the difference to target year to date, using the following:

YTD TARGET DIFF = CALCULATE(SUM(Orders[SALES]) - CALCULATE(SUM(Targetsales[SALES TARGET YTD])))

 

I now want to calculate the new monthly sales target, based on the YTD TARGET DIFF divided by the number of months remaining. I'm not sure whether to reference the other measures in the formula to achieve this, or if I can do it in one formula - for example:

 

NEW MONTHLY TARGET = Targetsales[YTD TARGET DIFF] - months remaining in current year

 

or

 

NEW MONTHLY TARGET = CALCULATE(SUM(Orders[SALES]) - CALCULATE(SUM(Targetsales[SALES TARGET YTD])) / months remaining in current year)

 

Please could someone help?

 

p.s. I'm new to measures and columns and don't know if there are limitations with using multiple measures or not.

 

 

1 ACCEPTED SOLUTION
KGrice
Memorable Member
Memorable Member

Hi @jasminwing. It looks like you're off to a good start. To help with figuring out the right approach, you can probably simplify some of your existing measures. For example, your YTD TARGET DIFF measure should do the same thing written like this:

 

YTD TARGET DIFF = SUM(Orders[SALES]) - [SALES TARGET YTD]

The CALCULATE function is used to modify how something is calculated by manipulating the filters applied to the calculation. If you're not using the second and other optional arguments, it's probably not needed, so you can take it off of the SUM(Orders[SALES]) part.

 

And since you've already defined SALES TARGET YTD as a measure, you don't have to do any recalculating on it. You don't even have to reference the table name for a measure; just use the measure name in [brackets]. This makes for great building blocks in future measures. If you already have a TOTAL SALES measure, you could add that as a building block as well:

 

TOTAL SALES = SUM(Orders[Sales])

YTD TARGET DIFF = [TOTAL SALES] - [SALES TARGET YTD]

For your NEW MONTHLY TARGET, you can then use:

 

NEW MONTHLY TARGET = [YTD TARGET DIFF] / (12 - MONTH(TODAY()))

 

View solution in original post

2 REPLIES 2
KGrice
Memorable Member
Memorable Member

Hi @jasminwing. It looks like you're off to a good start. To help with figuring out the right approach, you can probably simplify some of your existing measures. For example, your YTD TARGET DIFF measure should do the same thing written like this:

 

YTD TARGET DIFF = SUM(Orders[SALES]) - [SALES TARGET YTD]

The CALCULATE function is used to modify how something is calculated by manipulating the filters applied to the calculation. If you're not using the second and other optional arguments, it's probably not needed, so you can take it off of the SUM(Orders[SALES]) part.

 

And since you've already defined SALES TARGET YTD as a measure, you don't have to do any recalculating on it. You don't even have to reference the table name for a measure; just use the measure name in [brackets]. This makes for great building blocks in future measures. If you already have a TOTAL SALES measure, you could add that as a building block as well:

 

TOTAL SALES = SUM(Orders[Sales])

YTD TARGET DIFF = [TOTAL SALES] - [SALES TARGET YTD]

For your NEW MONTHLY TARGET, you can then use:

 

NEW MONTHLY TARGET = [YTD TARGET DIFF] / (12 - MONTH(TODAY()))

 

Hi @KGrice

 

Such a simple fix! Thank you so much, this worked perfectly.

 

Thank you also for the advice regarding CALCALATE and measures.

 

Jasmin

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.