Forum Discussion

sdhn's avatar
sdhn
Responsive Resident
4 years ago
Solved

Projected Percent Reduction

Hi All,

 

I have a report contains following FY matrices 

 

Now I want to calculate projceted value as:

 

  1. Add the values for each full months complete  from 2nd Half                                                                                                                            a) In this case Jan & Feb (124 + 120 =244)

At this time the value provided for March is not complete, therefore do not include it.

 

      2. Divide result by the number of full months completed                                                                                                                                      a) In this case there were 3 months completed, Jan, Feb & Mar.                                                                                                                b)244 / 2 = 122  ç this gives you the Average/Month

 

     3. Multiple by 6 months to determine the Total Projected Sum for that period.                                                                                             a)122 * 6 = 732  ç Gives you the Total Projected  Sum

 

To determine what the projected percentage is of 2nd half of the fiscal year when compared to the 1st half of the fiscal year:

  1. Multiply the Total Projected Sum of the 2nd half of the fiscal year by 100 and divide by the Total Sum of the 1st half of the fiscal year.                                                                                                                                                                                          a)732* 100 = 73200 / 948 = 77.21 %                                                                                                                                                            

 Total % Project Cost is 77.21 %

100-77.21 = 22.79 % is Projected Percent Reduction

 

Help will be appreciated.  Thanks 

Need Help
  • Anonymous's avatar
    Anonymous
    4 years ago

    Hi sdhn ,

     

    I have added a FY Flag column to table for the convenience:

    And firstly create a measure to get the current FY period (Current is 2022-March, so the FY period I set is 20221)

    CurrFY = YEAR(TODAY())*10+IF(MONTH(TODAY())>=7,2,1)

    Then please create measures:

    Measure 1 = CALCULATE(SUM('Table'[Value]),FILTER('Table',[FY Flag]=[CurrFY] && MONTH([Date])<MONTH(TODAY())))
    Measure 2 = CALCULATE(SUM('Table'[Value]) / DISTINCTCOUNT('Table'[Date].[MonthNo]),FILTER('Table',[FY Flag]=[CurrFY] && MONTH([Date])<MONTH(TODAY())))
    Measure 3 = [Measure 2] * 6
    Measure 4 = 
var _sumLastHalf=CALCULATE(SUM('Table'[Value]),FILTER('Table',[FY Flag]= MAXX(FILTER(ALL('Table'),[FY Flag]<[CurrFY]),[FY Flag])))
return [Measure 3] / _sumLastHalf
    Measure 5 = 1-[Measure 4]

    Output:

     

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

3 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    4 years ago

    Hi sdhn ,

     

    I have added a FY Flag column to table for the convenience:

    And firstly create a measure to get the current FY period (Current is 2022-March, so the FY period I set is 20221)

    CurrFY = YEAR(TODAY())*10+IF(MONTH(TODAY())>=7,2,1)

    Then please create measures:

    Measure 1 = CALCULATE(SUM('Table'[Value]),FILTER('Table',[FY Flag]=[CurrFY] && MONTH([Date])<MONTH(TODAY())))
    Measure 2 = CALCULATE(SUM('Table'[Value]) / DISTINCTCOUNT('Table'[Date].[MonthNo]),FILTER('Table',[FY Flag]=[CurrFY] && MONTH([Date])<MONTH(TODAY())))
    Measure 3 = [Measure 2] * 6
    Measure 4 = 
var _sumLastHalf=CALCULATE(SUM('Table'[Value]),FILTER('Table',[FY Flag]= MAXX(FILTER(ALL('Table'),[FY Flag]<[CurrFY]),[FY Flag])))
return [Measure 3] / _sumLastHalf
    Measure 5 = 1-[Measure 4]

    Output:

     

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

  • sdhn's avatar
    sdhn
    Responsive Resident
    4 years ago

    Hi Eyelyn

     

    Thansk for your nice reply. 

    I have few questions:

     

    FY Flag = YEAR([Date])*10+ IF(MONTH([Date]) >=7,2,1)
     
    1)  Why did you * 10
     
    2) what is 7, 2, 1 values for?
     
    3) Do I need to change Flag every month manually? 
     
    Thanks 

     

     

  • Anonymous's avatar
    Anonymous
    Not applicable
    4 years ago

    Hi sdhn ,

     

    1. I used Year*10  and the to make the year to the "front",so the format will be yyyyX.

       Like, if I use Year*100  + Month Number , the format will be yyyyMM. (202203=2022 March, 202211= 2022 November)

     

    2. For X, from your description, one year is divided to two parts ( half of the fiscal year)——

    First half: January to June 

    Second half:July to December

    So I used  if Month() >=7 then set 2 otherwise set 1.  2 means the second half ,1 means the first half.

    The final format of flag column will be yyyyX= 20221, 20222

     

    3. No.You don't need to manaully change it. I have used DAX to dynamically get the Flag.

     

     

    Hope my explanation could make it clear to you.😀

     

     

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