Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

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

Reply
EmaVasileva
Helper V
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
amitchandak
Super User
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])

View solution in original post

2 REPLIES 2
Datagulf
Responsive Resident
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.

Datagulf_0-1684762725394.png


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.

Datagulf_1-1684763172874.png

 

 


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

amitchandak
Super User
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])

Helpful resources

Announcements
LearnSurvey

Fabric certifications survey

Certification feedback opportunity for the community.

PBI_APRIL_CAROUSEL1

Power BI Monthly Update - April 2024

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

April Fabric Community Update

Fabric Community Update - April 2024

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