cancel
Showing results for
Did you mean:

Fabric is Generally Available. Browse Fabric Presentations. Work towards your Fabric certification with the Cloud Skills Challenge.

Frequent Visitor

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

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.

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

Regards,

Phaneendra Raavi

1 ACCEPTED SOLUTION
Super User

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.

2 REPLIES 2
Frequent Visitor

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

Super User

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.