Forum Discussion

maati1980's avatar
maati1980
Frequent Visitor
5 years ago
Solved

Monthly data and quarterly target calculation

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

Need Help
Show and Tell
Tips and Tricks
  • vanessafvg's avatar
    vanessafvg
    5 years ago

    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

     

     

5 Replies

  • vanessafvg's avatar
    vanessafvg
    Icon for Community Champion rankCommunity Champion
    5 years ago

    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

     

     

  • maati1980's avatar
    maati1980
    Frequent Visitor
    5 years ago

    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's avatar
      maati1980
      Frequent Visitor
      5 years ago

      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

  • Anonymous's avatar
    Anonymous
    Not applicable
    5 years ago

    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.

     

     

  • Harpreet1405_12's avatar
    Harpreet1405_12
    Icon for Helper III rankHelper III
    3 years ago

    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