Measue - Rolling Total Measure Pulling from 2 Tables based on Date

[powerbi1980]

Hi,

I would like to have a measure that presents a rolling total of Actual Labour (in the past) AND Future Planned Labour.

We have a FactLabour table w/ actual labour and a m:1 relationship with DimDate

We have a FactPlannedLabour table /w planned labour and a m:1 relationship with DimDate

I would like to present in a line graph, with the date as the x axis, one line that shows rolling totals of actual labour amounts for days passed, and planned labour amounts for future dates.

I have measures to calculate the rolling total sum of actuals and planned based on date. I want these to be combined in essence:

Cumulative Planned Labor = CALCULATE (  SUM ( FactPlanLabour[PeriodBill] ),

                                                                     FILTER( ALLSELECTED( FactPlanLabour ),

                                                                     FactPlanLabour[DimDateID] <= MAX( FactPlanLabour[DimDateID] ) ) )

Cumulative Labor = CALCULATE ( SUM ( FactLabour[BillAmount] ),

                                                      FILTER( ALLSELECTED( FactLabour ),

                                                      FactLabour[DimDateID] <= MAX( FactLabour[DimDateID] ) ) )

Basically, the blue line below, where the grey bar represents today. The dotted portion is the rolling total of the planned labour, and the solid portion is the rolling total of the actual labour

So how can I calulate this? I tried the measure below, but I recognize it won't work as the planned portion will not sum the actual labour.

Cumulative Actual or Planned Labour =  IF( Today()>ENDOFMONTH(DimDate[Date]),  

                                                                              CALCULATE ( SUM ( FactLabour[BillAmount] ),

                                                                                                    FILTER( ALLSELECTED( FactLabour ),

                                                                                                    FactLabour[DimDateID] <= MAX( FactLabour[DimDateID] ) ) ),                                                                                          CALCULATE ( SUM ( FactPlanLabour[BillAmount] ),

                                                                                                    FILTER( ALLSELECTED( FactPlanLabour ),

                                                                                                    FactPlanLabour[DimDateID] <= MAX( FactPlanLabour[DimDateID] )))                                                                        )

[DataInsights]

@powerbi1980,

See if you can adapt the solution in the post below. It uses a blended measure that sums the two underlying measures, using Actual when available and Forecast for the remainder.

https://community.fabric.microsoft.com/t5/Desktop/Retrieve-data-in-measure-from-different-tables-bas...