cancel
Showing results for
Did you mean:

Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.

Helper V

## Dax measure actual/planned

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.

1 ACCEPTED SOLUTION
Super User

@EmaVasileva , 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])

2 REPLIES 2
Responsive Resident

Hey @EmaVasileva , 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

Super User

@EmaVasileva , 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])

Announcements

#### New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.

#### Power BI Monthly Update - May 2024

Check out the May 2024 Power BI update to learn about new features.

#### Fabric certifications survey

Certification feedback opportunity for the community.

Top Solution Authors
Top Kudoed Authors