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

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

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.

Super User

@EmaVasileva , Try these measures

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

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

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

Announcements

#### Fabric certifications survey

Certification feedback opportunity for the community.

#### Power BI Monthly Update - April 2024

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

#### Fabric Community Update - April 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors
Top Kudoed Authors