Smartest way to model my data ? Plans vs Actuals

[stewbel]

Hi I have a powerquery that merges plans and actuals but need a clever way to deal with overlap.

For example in Dec, I want to show Actuals for Jan-Nov and Plans for Dec. I can do this by filtering the source tables for append but this seems kinda painful.  Any suggestions ? (~ are for reading)

TblPlan

Person	Project	Month	Hrs	Src
Joe~	Pixiedust~	11/01/2024~	20~	Plan
Joe~	Pixuedust~	12/01/2024~	40~	Plan

TblActuals (As of Week 1 in December)

Person	Project	Month	Hrs	Src
Joe~	Pixiedust~	11/01/2024~	30~	Actuals
Joe~	Pixuedust~	12/01/2024~	5~	Actuals

[Chewdata · Accepted solution]

Hey!

Create a table that has both the rows for src Plan and Actuals in it.
(created some extra rows for the example).

With COALESCE() you can switch to a next variable (or measure) if a variable returns a null value. 

The measure will look like this:

Hours = 
var __actuals =
CALCULATE(
    SUM(Hours[Hrs]),
    Hours[Src] = "Actuals"
)

var __plans = 
CALCULATE(
    SUM(Hours[Hrs]),
    Hours[Src] = "Plan"
)

RETURN
// if __actuals returns null then try __plans. If __plans returns null then return 0.
COALESCE(__actuals, __plans, 0)

Outcome:

I hope this is helpfull! If so please accept it as a solution, so other users can benefit from it aswell.

[Omid_Motamedise]

Hi @stewbel 

If you want to do it in Power Query, use Date.LocalNow to extract today date, then use  Table.SelectRows twice, one for filtering actual table (previously mounth) and one for plan table, and finally use Table.Group to combine the result of both tables

[stewbel]

This is super useful - Thank you

[lbendlin]

No need.  Use COALESCE to try to use the actuals and if they are not present then use the plan.

By the way this has nothing to do with modeling yet.