Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Compete to become Power BI Data Viz World Champion! First round ends August 18th. Get started.

Reply
PrasadRP
Frequent Visitor

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 * WP) each month.

3. Find the number of months completed + 1 (Count Months).
4. Create the logic to find the HTD Weighted average by sum of values divide by setp 3.

Logic 1
No of months in HTD 3   
      
Month CompletedSalesWeighting PeriodSales * WPCount MonthsTo be calculate
in the Power BI
  4   
Jan20360230
Feb45290350
Mar27127444.25
Apr 0   
May 0   
Jun 0   
      
OutPut HTD Weighted Avg Sales44.25 TRUE

 

Logic 2
No of months in HTD 4   
      
Month CompletedSalesWeighting PeriodSales * WPCount MonthsTo be calculate
in the Power BI
  5   
Jan20480240.00
Feb453135371.67
Mar27254467.25
Apr32132560.20
May 0   
Jun 0   
      
  HTD Weighted Avg Sales60.20 TRUE


Kindly help me on this, i was working this since few weeks but not able to findout the way to create in power BI, 

Many thanks in advance..!

3 REPLIES 3
Anonymous
Not applicable

Hi  @PrasadRP ,

 

Here are the steps you can follow:

1. Create calculated column.

 

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

 

vyangliumsft_0-1713148553136.png

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:

vyangliumsft_1-1713148553138.png

 

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

IMG_20240415_125749.jpg

 My current logic to calculate dynamic Weighted Period 

IMG_20240415_123921.jpg

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?

PrasadRP
Frequent Visitor

Hi Everyone,

 

Could you please help on this calculation please?

Helpful resources

Announcements
August Power BI Update Carousel

Power BI Monthly Update - August 2025

Check out the August 2025 Power BI update to learn about new features.

August 2025 community update carousel

Fabric Community Update - August 2025

Find out what's new and trending in the Fabric community.