Measure to combine past and future

[frankhofmans]

hi Pbi experts,

i have three tables, a date table, an actuals table and a capacity table:

Actuals table

Date	ID	# Hours
01-01-2024	O111	10
01-02-204	O112	15
01-03-2024	O113	12
20-03-2024	O114	20
01-05-2024	O115	12
01-06-2024	O116	15
01-07-2024	O117	14

Capacity table

Date	Capacity ID	Hours
01-01-2024	C111	15
01-02-2024	C112	20
01-03-2024	C113	17
01-04-2024	C114	20
01-05-2024	C115	21
01-06-2024	C117	22
01-07-2024	C118	23
01-08-2024	C119	21
01-09-2024	C120	22

Both tables have a direct relation with the date table. I want to creature a graph with 1 line which combines past and future. So for all selected dates before today i want to show the actuals hours and for all selected dates after today i want to show the capacity hours. So if it's feb. 12th and i select 2024, from jan. 1 till feb. 12 i want to show the actuals and from feb. 13 to dec. 31 i want to show the capacity hours, in one line.

Do you have an solution for me?

Many thanks in advance,

Regards, Frank

[timalbers · Accepted solution]

Hi @frankhofmans 

you could create a measure like the following:

new_measure = 
    SWITCH( TRUE(),
        MIN( date_table[Date] ) <= TODAY(), SUM( Actuals[Hours] ),
        SUM( Capacity[Hours] )
    )

Cheers

Tim

_____

If this post helped you, please consider marking it as solution, thank you!

[TDK · Accepted solution]

Hi Frank,

This should work and will show actuals when available and capacity when actuals are not available, this would not be limited capacity for after today, meaning that even if today’s data is missing it will show something :).

Actuals and Capacity =
//this shows actuals if available, and defaults to showing capacity 
var _actuals = SUM('Actuals'[Hours])
var _capacity = SUM('Capacity'[Hours])
RETURN
if(ISBLANK(_actuals), _capacity,_actuals)

If you need this to specifically draw the line on today the following would do that:

Actuals and Capacity =
//this shows actuals for dates up to today, and after today show values from capacity
var _actuals = CALCULATE(SUM('Actuals'[Hours]),FILTER('your_date_table',[Date] <= Today()))
var _capacity = CALCULATE(SUM('Capacity'[Hours]),FILTER('your_date_table',[Date] > Today()))
RETURN
_capacity+_actuals

My only caveat with these solutions is as soon as you filter using a value from Actuals or Capacity, your graph will show values only from either and not from both. Shout if this is an issue and I can help with an alternative :).

Thanks,

Tian

[timalbers · Accepted solution]

Hi @frankhofmans 

you could create a measure like the following:

new_measure = 
    SWITCH( TRUE(),
        MIN( date_table[Date] ) <= TODAY(), SUM( Actuals[Hours] ),
        SUM( Capacity[Hours] )
    )

Cheers

Tim

_____

If this post helped you, please consider marking it as solution, thank you!