how to calculate year wise product cost data by monthly headcount data every month and get YTD

[powanpilla]

I have two tables one is headcount its is in month wise every month there will be new data with historical data (data of all months in a year)and i have product cost table data which is year wise (just one sinlge date as 01/03/2022)for that year i have just single values. now i have to calculate the Product cost by headcount everymonth and to get YTD of that new Pex cost 

headcount values product cost value date Pex Cost Date YTD Pex cost 01/01/2022 10 01/12/2022 5000 01/01/2022 500 01/01/2022 500 01/02/2022 20     01/02/2022 250 01/02/2022 750 01/03/2022 30 formula E2/B3 01/03/2022 166.6666667 01/03/2022 916.6666667 01/04/2022 40 PEX cost Yearly Product cost/monthly Headcount 01/04/2022 125 01/04/2022 1041.666667 01/05/2022 50     01/05/2022 100 01/05/2022 1141.666667 01/06/2022 60     01/06/2022 83.33333333 01/06/2022 1225 01/07/2022 70     01/07/2022 71.42857143 01/07/2022 1296.428571 01/08/2022 80     01/08/2022 62.5 01/08/2022 1358.928571 01/09/2022 90     01/09/2022 55.55555556 01/09/2022 1414.484127 01/10/2022 100     01/10/2022 50 01/10/2022 1464.484127 01/11/2022 110     01/11/2022 45.45454545 01/11/2022 1509.938672 01/12/2022 120     01/12/2022 41.66666667 01/12/2022 1551.605339 01/01/2023 130     01/01/2023 38.46153846 01/01/2023 1590.066878 01/02/2023 140     01/02/2023 35.71428571 01/02/2023 1625.781163 01/03/2023 150     01/03/2023 33.33333333 01/03/2023 1659.114497

[v-zhangti · Accepted solution]

Hi, @powanpilla 

You can try the following methods.

Measure:

Pex Cost = 
Var _value=5000
Return
DIVIDE(_value,SUM('Table'[values]))

YTD Pex cost = SUMX(FILTER(ALL('Table'),[Date]<=SELECTEDVALUE('Table'[Date])),[Pex Cost])

Is this the result you expect?

Best Regards,

Community Support Team _Charlotte

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[powanpilla]

@mlsx4 @parry2k @Idrissshatila @amitchandak @Greg_Deckler @HarishKM @Ritaf1983 @Ahmedx Please Help me