Calculate monthly savings....DAX Help needed... Urgent!!!

[phaneendra]

Hi There,

I am struck with an issue. Could someone please help me resolve the issue?

I have a data table as follows which has monthly headcount & AVG C&B (monthly CTC) data:

Month	Headcount	Avg C&B
31-01-2021	20	833.33
28-02-2021	15	833.33
31-03-2021	15	833.33
30-04-2021	15	833.33
31-05-2021	12	833.33
30-06-2021	15	833.33
31-07-2021	15	833.33
31-08-2021	15	833.33
30-09-2021	10	833.33
31-10-2021	15	833.33
30-11-2021	15	833.33
31-12-2021	10	833.33

The out needed is as follows:

Jan

Feb

Mar

Apr

May

Jun

Jul

Aug

Sep

Oct

Nov

Dec

HC

20

15

15

15

12

15

15

15

10

15

15

10

Avg C&B

₹           833.33

₹           833.33

₹           833.33

₹           833.33

₹           833.33

₹           833.33

₹           833.33

₹           833.33

₹           833.33

₹           833.33

₹           833.33

₹           833.33

CTC

₹           16,667

₹           12,500

₹           12,500

₹           12,500

₹           10,000

₹           12,500

₹           12,500

₹           12,500

₹              8,333

₹           12,500

₹           12,500

₹              8,333

₹ 1,43,333

Savings

₹                     -

₹              4,167

₹              4,167

₹              4,167

₹              6,667

₹              4,167

₹              4,167

₹              4,167

₹              8,333

₹              4,167

₹              4,167

₹              8,333

₹     56,667

The calculations are as follows:

CTC for the month = HC * C&B

Savings for Jan = 0 since its the start of the month

Savings for Feb  = Jan CTC - Feb CTC

Savings for Mar = Jan CTC - Mar CTC

and so on...

When trying to replicate the same in Power BI, I was able to get the right values for Jan, and for the rest of the months, I was not able to bring the desired result.

Could you please help me resolve the issue? 

@rohit_singh, It would be of great help if you could assist me with this issue if possible.

Thanks in advance

Regards,

Phaneendra Raavi

[phaneendra]

Thankyou @amitchandak 

Your solution worked big time.

Yes, we have a calendar table, for which I have made some modifications basis the original data, assigned rank and picked up the corresponding month value.

Thank you so much for taking time to help me out!

Means a lot!!

Highly appreciated 🙂

This originated 

[amitchandak · Accepted solution]

@phaneendra ,

If they are columns

new measure 

CTC = sumx(Table, [Headcount] * [Avg C&B] )

if measures then new measure

CTC = CTC = sumx(values(Table[Month]) , [Headcount] * [Avg C&B] ) 

Assuming you have month start date or end date 

You need Jan CTC =

calculate([CTC], Eomonth([Month Date] ,0)  = Eomonth([Month Date] , ( -1* month([Month Date]) )+1)  )

It will be better if month date comes from a date table

If this does not help
Can you share sample data and sample output in table format? Or a sample pbix after removing sensitive data.