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

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
powerbi1980
Advocate I
Advocate I

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

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
 
powerbi1980_0-1681758514628.png

 

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 =  IFToday()>ENDOFMONTH(DimDate[Date]),  
                                                                              CALCULATESUM ( FactLabour[BillAmount] ),
                                                                                                    FILTER( ALLSELECTED( FactLabour ),
                                                                                                    FactLabour[DimDateID] <= MAX( FactLabour[DimDateID] ) ) ),                                                                                          CALCULATESUM ( FactPlanLabour[BillAmount] ),
                                                                                                    FILTER( ALLSELECTED( FactPlanLabour ),
                                                                                                    FactPlanLabour[DimDateID] <= MAX( FactPlanLabour[DimDateID] )))                                                                        )
1 REPLY 1
DataInsights
Super User
Super User

@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... 





Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

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