topic Re: HTD Weighted Average Calculation based on months completed in DAX Commands and Tips

HTD Weighted Average Calculation based on months completed

PrasadRP — Thu, 11 Apr 2024 12:57:54 GMT

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 Completed	Sales	Weighting Period	Sales * WP	Count Months	To be calculate in the Power BI
		4
Jan	20	3	60	2	30
Feb	45	2	90	3	50
Mar	27	1	27	4	44.25
Apr		0
May		0
Jun		0

OutPut		HTD Weighted Avg Sales	44.25		TRUE

Logic 2
No of months in HTD		4

Month Completed	Sales	Weighting Period	Sales * WP	Count Months	To be calculate in the Power BI
		5
Jan	20	4	80	2	40.00
Feb	45	3	135	3	71.67
Mar	27	2	54	4	67.25
Apr	32	1	32	5	60.20
May		0
Jun		0

		HTD Weighted Avg Sales	60.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..!

Re: HTD Weighted Average Calculation based on months completed

PrasadRP — Fri, 12 Apr 2024 10:34:30 GMT

Hi Everyone,

Could you please help on this calculation please?

Re: HTD Weighted Average Calculation based on months completed

Anonymous — Mon, 15 Apr 2024 02:38:41 GMT

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

Re: HTD Weighted Average Calculation based on months completed

PrasadRP — Mon, 15 Apr 2024 07:30:17 GMT

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?