Calculate variances between various columns depending on the value date vs current date

[garrettmindrup]

Hello, 

I have two tables, one is a Target Amounts table which has a Target Amount and an Outlook Amount. The other one is an Actuals Amounts Table, that has the Actual Amounts in it (Both shown below). These fact values have corresponding 1-many relationships with some dimension tables, so all data can line up as needed. 

I need to write a measure that returns a value based on the following logic: 

• If the amounts are within 6 months of the current date then:
  • IF: 
    • Target Amount > Actuals Amount, return Target Amount - Outlook Amount
    • Else, Return Actuals Amount - Outlook Amount
• Else, return Actuals Amount - Outlook Amount

Intended result:

So, if the current date is July 2023, then the measure would return the results in the far right column in the picture below. 

This is the formula I tried. The results are significantly larger than what was intended (hundreds of Billions in variances when it should be 10s of millions in variances).  Any ideas on what I need to tweak? 

OutlookVarianceCalculation = 
//Create variables
VAR CurrentDate = TODAY() //Current date variable
VAR SixMonthsAgo = EDATE(CurrentDate, -6) //6 months prior to current date
VAR TargetAmount = SUM(Cumulative_Targets[Current Month Net Target]) //Target amount
VAR ActualsAmount = SUM(CMS_Gross_Actuals[Net_Amount]) //Actuals Amount
VAR OutlookAmount = SUM(Cumulative_Targets[Outlook]) //Outlook Amount
RETURN

SUMX(
    FILTER( //Filter table on dates within the past 6 months
        ALL('Date Dim'),
        'Date Dim'[First_Of_Month] >=SixMonthsAgo && 'Date Dim'[First_Of_Month] <= CurrentDate
    ),
    IF( //Return either target vs outlook variance or actuals vs outlook variance based on the logic below
        TargetAmount > ActualsAmount, //logic
        TargetAmount - OutlookAmount, // return if true
        ActualsAmount - OutlookAmount // return if false
    )
)
+
SUMX( //Also sum variances past 6 months using the actuals - outlook logic.
    FILTER(
        ALL('Date Dim'),
        'Date Dim'[First_Of_Month] < SixMonthsAgo || 'Date Dim'[First_Of_Month] > CurrentDate
    ),
    ActualsAmount - OutlookAmount
)

Tables:

Actuals Table

Service Month	Posting Date	Region	Sub Region	Reporting Category	Actuals Amount
1/1/2023 0:00	1/1/2023 0:00	Region 1		Cat 1	$503,972
2/1/2023 0:00	2/1/2023 0:00	Region 1		Cat 1	$1,099,065
1/1/2023 0:00	3/1/2023 0:00	Region 1		Cat 2	$86,573
2/1/2023 0:00	4/1/2023 0:00	Region 1		Cat 2	$10,277
2/1/2023 0:00	5/1/2023 0:00	Region 1		Cat 2	($42,746)

Target Table

Service Date	Region	Sub Region	Reporting Category	Target Amount	Outlook Amount
1/1/2023 0:00	Region 1		Cat 1	$600,000	550,000.00
2/1/2023 0:00	Region 1		Cat 1	$1,000,000	900,000.00
1/1/2023 0:00	Region 1		Cat 2	$80,000	90,000.00
2/1/2023 0:00	Region 1		Cat 2	$10,000	10,000.00
2/1/2023 0:00	Region 1		Cat 2	($45,000)	(50,000.00)

Table Relationships:

Each dimension has a dimension table that has a 1-Many relationship with the fact table. The dates used in my dax formula come form my date dimension table.  

[some_bih]

Hi @garrettmindrup some usefull links: excel power pivot

and dax pattern

It is very important to have really good model for budget / forecast before creating measures, so please check it, if not already..