cancel
Showing results for
Did you mean:

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

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

#### Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.