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" )
)
) )



Features || Datamarts: https://youtu.be/8tskWsJTEpg || Field Parameters : https://youtu.be/lqF3Wa1FllE?t=70
Time Intelligence Decoded : https://youtu.be/aU2aKbnHuWs&t=145s || Learn 50+ Power Query List Functions
Did I answer your question? Mark my post as a solution! Appreciate your Kudos !! Proud to be a Super User! !!
Dashboard of My Blogs !! Connect on Linkedin !! Subscribe to my youtube Channel !!!! How to get Quick Help !!
Resources | Master Power BI !! Expertise Power BI !! Power BI For Tableau User !!

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

Helpful resources

Announcements
collabdays lisbon

CollabDays Lisbon - 26 November 2022

Sessions include practical, hands-on experience that will help you take the next step in your career and know-how.

Difinity Conference

Difinity Conference 2022

Difinity is the largest Microsoft Data, AI, Power BI, Power Platform and Business Applications Conference in New Zealand.

European Share Point Conference

European Share Point Conference

This conference returns live and in-person in Copenhagen this 28 November-01 December with 4 Microsoft Keynotes, 9 Tutorials, and 120 Sessions.

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.