cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
raymondpocher
Advocate II
Advocate II

Combine actual and target based on date

I want to create a simple measure but I fail to get it.

 

There are actual and plan values. I want to create a measure that sums actual values as soon as there are any values. If there is no actual value what so over the measure should use the plan value.

2021-11-17 10_22_25-Window.jpg

 

I have tried to write a measure but it is not working. 

 

New Measure = 
var actual_values_maxDate = 
CALCULATE(
    MAX( 'Table'[Date] ),
    ALL( 'Table' ),
    'Table'[Planning Type] = "actual"
)

RETURN
IF(
    //Logical Test
    SELECTEDVALUE( 'Date'[Date] ) > actual_values_maxDate,
    //if true
    CALCULATE(
            SUM( 'Table'[Value] ),
            FILTER( 'Table', 'Table'[Planning Type] = "plan" )
    ),
    //if false
        CALCULATE(
            SUM( 'Table'[Value] ),
            FILTER( 'Table', 'Table'[Planning Type] = "actual" )
        )
)

 

 

1 ACCEPTED SOLUTION
v-xiaotang
Community Support
Community Support

Hi @raymondpocher 

Based on your example, you can try this measure, 

 

measure = 
    var _actual= MIN('Table'[Actual])
    var _plan= MIN('Table'[Plan])
return IF(ISBLANK(_actual),_plan,_actual)

 

vxiaotang_0-1637567295862.png

 

 

Best Regards,

Community Support Team _Tang

If this post helps, please consider Accept it as the solution to help the other members find it more quickly.

View solution in original post

4 REPLIES 4
v-xiaotang
Community Support
Community Support

Hi @raymondpocher 

Based on your example, you can try this measure, 

 

measure = 
    var _actual= MIN('Table'[Actual])
    var _plan= MIN('Table'[Plan])
return IF(ISBLANK(_actual),_plan,_actual)

 

vxiaotang_0-1637567295862.png

 

 

Best Regards,

Community Support Team _Tang

If this post helps, please consider Accept it as the solution to help the other members find it more quickly.

@v-xiaotang Thank you. That seemed to work.

 

I have adjusted the formular a little bit according to my case

 

FTE = 
value instead. 
var _actual= CALCULATE( SUM('Internal'[FTE] ),'Internal'[Planning Type] = "actual" )
var _plan= CALCULATE( SUM('Internal'[FTE] ),'Internal'[Planning Type] = "plan" )
RETURN
IF(
    //logical test
    ISBLANK(_actual),
    //if true
    _plan,
    //if false
    _actual
)

 

amitchandak
Super User
Super User

@raymondpocher , Try with small change

 

New Measure =
var actual_values_maxDate =
CALCULATE(
MAX( 'Table'[Date] ),
Filter( ALL( 'Table' ),
'Table'[Planning Type] = "actual" && not(isblank('Table'[Value] )) )
)

RETURN
Sumx( values(Table[Year]), IF(
//Logical Test
Max( 'Date'[Date] ) > actual_values_maxDate,
//if true
CALCULATE(
SUM( 'Table'[Value] ),
FILTER( 'Table', 'Table'[Planning Type] = "plan" )
),
//if false
CALCULATE(
SUM( 'Table'[Value] ),
FILTER( 'Table', 'Table'[Planning Type] = "actual" )
)
) )

@amitchandak  I dont know why but for some reason I wasnt able to manage it with that formular.

Helpful resources

Announcements
November 2022 Update

Check it Out!

Click here to read more about the November 2022 updates!

Microsoft 365 Conference â__ December 6-8, 2022

Microsoft 365 Conference - 06-08 December

Join us in Las Vegas to experience community, incredible learning opportunities, and connections that will help grow skills, know-how, and more.

Power BI Dev Camp Session 27

Ted's Dev Camp

This session walks through creating a new Azure AD B2C tenant and configuring it with user flows and custom policies.