Forum Discussion

PrasadRP's avatar
PrasadRP
Frequent Visitor
2 years ago

HTD Weighted Average Calculation based on months completed

Process to calculate HTD Weighted average: 1. Need to create the weighting period based on the no of months completed with in "Half Year". 2. Multiply the sales measure with Weighting period (Sales...
Anonymous's avatar
Anonymous
Not applicable
2 years ago

Hi  PrasadRP ,

 

Here are the steps you can follow：

1. Create calculated column.

 

Month =
FORMAT('Table'[Date],"mmm")
Month_Number =
MONTH('Table'[Date])

 

2. Create measure.

 

Weighting Period =
var _Weight=
MAXX(ALL('Table'),'Table'[Month_Number])
return
IF(
    MAX('Table'[Month_Number])=1,_Weight,
    _Weight-MAX('Table'[Month_Number])+1)
Value_All =
var _Sales=
SUMX('Table',[Sales])
var _SalesWP=
_Sales * [Weighting Period]
return
_SalesWP
Measure =
var _Count=
MAX('Table'[Month_Number])+1
var _Sum=
SUMX(
    FILTER(ALLSELECTED('Table'),[Month_Number]<=MAX('Table'[Month_Number])),[Value_All])
return
DIVIDE(
    _Sum,_Count)

 

3. Result:

 

Best Regards,

Liu Yang

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

  • PrasadRP's avatar
    PrasadRP
    Frequent Visitor
    2 years ago

     My current logic to calculate dynamic Weighted Period 

     

    Hi Anonymous

     

    Thank you so much for prompt response on my query, I have used this login in my report and still it's not working as I expected, 

     

    I would like to add another point here, I need to calculate Weighted period and month numbers for Half Year(HTD) like as per attached image. As per your logic its calculating for full year.

    Important weighted period and count of completed months calculate would dynamic. Like if if user selected any random months then it should consider Max complete month within that HY.

     

    Kindly advise how I can modify this logic to HTD?