Fabric is Generally Available. Browse Fabric Presentations. Work
towards your Fabric certification with the Cloud Skills Challenge.
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:
The out needed is as follows:
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
Go to Solution.
If they are columns
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 helpCan you share sample data and sample output in table format? Or a sample pbix after removing sensitive data.
View solution in original post
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 🙂
Check out the November 2023 Power BI update to learn about new features.
130+ sessions, 130+ speakers, Product managers, MVPs, and experts. All about Power BI and Fabric. Attend online or watch the recordings.