Monthly data and quarterly target calculation

[maati1980]

Hello community,

I'm not and expert in powerbi but I'm not also a newbie. Last week I had a task to prepare and I'm looking for some clever and smart solution.

In first table I have column date and monthly sales in second I have date and target sales for current year. The problem is that targets are set on quarterly basis but sales is noted monthly. For Jan-mar I should calculate target Q1 vs LY Q1 growth, for Apr-Jun I should calculate H1 vs LY H1 growth, for Jul-Sep target should be Q3 YTD vs Last year Q3 YTD, for Oct to Dec It should be FY vs Last year FY.

Is there any fancy way to calculate this?

I don't want to make to many nested if's...

Thanks for your help

maati

[Harpreet1405_12]

Hello,

I need your help to set the targets for the upcoming months (Oct 2022- SEPT 2023) BASED on the historical data,  I have the historical data from 2019 to 2022, so we go like Oct 2020-Sept 2021, Oct 2021-Sept 2022, Oct 2022-Sept 2023(I want to set the targets for these months) for 80 different programs or categories.

Is There any formula or any Dax function available.

I will highly appreciate the response.

Thank you

[Anonymous]

Hi @maati1980 ,

You can create a seperate table like

Then create the measure

Measure = 
var _quarternumber=SUMMARIZE(ALLSELECTED('Table (2)'),'Table (2)'[Quarter Number])
return 
 IF(QUARTER(MAX('Table'[Date])) in _quarternumber && (YEAR(MAX('Table'[Date]))= YEAR(TODAY()) || YEAR(MAX('Table'[Date])) = YEAR(TODAY())-1) ,1,0)

Put the measure into Filters and set as follows

You can check more details from the attachment.

Best Regards,

Stephen Tao

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[maati1980]

Ok, I created a time table. In one column I have first day of each month in second first day of the last month of each quarter. I've made relationship that the target table is linked to the second column of time table.

When I create a measure that calculates Actual Sales growth I use formula:

= SUM('ActualTable'[Sales]/CALCULATE(SUM('ActualTable'[Sales]),SAMEPERIODLASTYEAR('Date'[Time])

and this works for actual sales growth. I can filter any date and I see proper growth rates.

How should look like formula to calcluate target sales growth?

My idea is to be able to filter any month and show actuals sales growth (done) vs target for current quarter.

Thank you for your patience.

maati

[maati1980]

Ok, I created a time table. In one column I have first day of each month in second first day of the last month of each quarter. I've made relationship that the target table is linked to the second column of time table.

When I create a measure that calculates Actual Sales growth I use formula:

= SUM('ActualTable'[Sales]/CALCULATE(SUM('ActualTable'[Sales]),SAMEPERIODLASTYEAR('Date'[Time])

and this works for actual sales growth. I can filter any date and I see proper growth rates.

How should look like formula to calcluate target sales growth?

My idea is to be able to filter any month and show actuals sales growth (done) vs target for current quarter.

Thank you for your patience.

maati

[vanessafvg · Accepted solution]

first you need to create a date table, then you link both of your tables to the date table. 

in your date table you have the required fields to link each date from your other tables at the specific grain required, ie quarter and month

use this video to get our started https://www.youtube.com/watch?v=-li7sxUxEqA

then you are able to roll up your data using the date table to track againt targets