Dax measure actual/planned

[Anonymous]

Hello team,

I have the following data:

Date	Tasks	Planned	Fulfilled
04.04.2023	A	2	2
30.04.2023	B	6	4
02.05.2023	A	4	4
15.05.2023	B	5	4
30.05.2023	C	8

The planned tasks are given on the 1st of the curent month until the end of month, and the fulfilled task are updated every day. 

I need help with a measure, which calculates the sum of the the fulfilled tasks until the last completed day vs. the sum of planned until the end of month. 

For ex. for May (or any current month) - I need measure to calculate fulfilled (4+4) divided by planned (4+5+8). For every past month the measure need to calculate the total sum of fulfilled/planned in the EOM.

Thank you.

[Anonymous]

Hey @Anonymous , do the following. 

1, Open Power Query and add a Custom Column

Date.MonthName([Date])

Then Select that Month Column and Unpivot other columns. You get this.

Then use the following formula.

Workforce = 
VAR SumPlanned = CALCULATE(SUM('Table (2)'[Value]),FILTER('Table (2)','Table (2)'[Attribute]= "PLANNED"),GROUPBY('Table (2)','Table (2)'[Month]))
VAR SUMFulfilled =CALCULATE(SUM('Table (2)'[Value]),FILTER('Table (2)','Table (2)'[Attribute]= "FULFILLED"),GROUPBY('Table (2)','Table (2)'[Month]))
VAR Division = SumPlanned/SUMFulfilled

Return 
Division

Your result will be as follows.

If you find this helpful. Please like and accept as solution

[amitchandak · Accepted solution]

@Anonymous , Try these measures

Last Planned = Var _max = maxx(filter( ALLSELECTED(Data1), Data1[Task] = max(Data1[Task]) ),Data1[Date])
return
CALCULATE(sum(Data1[Planned]), filter( (Data1), Data1[Task] = max(Data1[Task]) && Data1[Date] =_max))

Sum Last Planned = sumx(VALUES(Data1[Task]) , [Planned])

Last Fulfilled = Var _max = maxx(filter( ALLSELECTED(Data1), Data1[Task] = max(Data1[Task]) && not(isblank([Fulfilled])) ),Data1[Date])
return
CALCULATE(sum(Data1[Fulfilled]), filter( (Data1), Data1[Task] = max(Data1[Task] && not(isblank([Fulfilled]))) && Data1[Date] =_max))

Sum Last Fulfilled = sumx(VALUES(Data1[Task]) , [Fulfilled])