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

Anonymous — Thu, 25 Jan 2024 21:19:58 GMT

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.

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

some_bih — Thu, 25 Jan 2024 23:05:06 GMT

Hi Anonymous 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..

topic Calculate variances between various columns depending on the value date vs current date in DAX Commands and Tips

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

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