Forum Discussion

Frequent Visitor

3 years ago

Solved

Help with Dynamic Actual Amounts - Comparing 2 Forecast Plans

Hi,

I have a problem where I need to be able to compare 2 different "Plans" with YTD #s.

So the way these are set up, the naming convention is "[Year] [Month Number (Actuals)]+(12-[Month Number (Forecast)]. So an example is "2022 1+11", which means 2022 Forecast with 1 months of actuals and 11 months forecast.

Where I'm running into a problem is what I need to be able to compare is 2 different plans via 2 slicers. The 1st one would be a given monthly plan (say "2022 3+9" - 3months actuals, 9 months fcst). The 2nd one in most cases would be the annual budget plan.

I created the 1st measure with no problem since I am defining exactly what I want to be YTD actuals, the problem is with the 2nd Plan. What I need is for the measure to say "Treat any Months defined as actuals in Slicer 1 as Months being actuals in Slicer 2". Obviously if we are comparing 2 different plans that is not the case but I want to be able to compare apples to apples.

Here is an example of data (simplified), any help is greatly appreciated!

Data:
Project	Cost Plan	Amt	Month	Year	Actuals Thru
A	2022 3+9	500	1	2022	3/1/2022
A	2022 3+9	300	2	2022	3/1/2022
A	2022 3+9	200	3	2022	3/1/2022
A	2022 3+9	100	4	2022	3/1/2022
A	2022 Budget	800	1	2022	No Actuals
A	2022 Budget	300	2	2022	No Actuals
A	2022 Budget	900	3	2022	No Actuals
A	2022 Budget	500	4	2022	No Actuals

Required Result
Project	Plan Selection	Actuals
A	2022 3+9	1000
A	2022 Budget	2000

Try creating a disconnected table for each plan (no relationships). You can do this in Power Query or DAX. Here are the calculated tables:

CostPlan1 = DISTINCT ( FactTable[Cost Plan] )

CostPlan2 = DISTINCT ( FactTable[Cost Plan] )

Create a slicer for each of these tables (Plan 1, Plan 2).

Measures:

Plan 1 Amount YTD = 
// get Actuals Thru for first plan and convert from text to date
VAR vActualsThru =
    CALCULATE (
        DATEVALUE ( SELECTEDVALUE ( FactTable[Actuals Thru] ) ),
        TREATAS ( CostPlan1, FactTable[Cost Plan] )
    )
VAR vDatesYTD =
    DATESBETWEEN (
        DimDate[Date],
        DATE ( YEAR ( vActualsThru ), 1, 1 ),
        vActualsThru
    )
VAR vResult =
    CALCULATE (
        SUM ( FactTable[Amt] ),
        vDatesYTD,
        TREATAS ( CostPlan1, FactTable[Cost Plan] )
    )
RETURN
    vResult

Plan 2 Amount YTD = 
// get Actuals Thru for first plan and convert from text to date
VAR vActualsThru =
    CALCULATE (
        DATEVALUE ( SELECTEDVALUE ( FactTable[Actuals Thru] ) ),
        TREATAS ( CostPlan1, FactTable[Cost Plan] )
    )
VAR vDatesYTD =
    DATESBETWEEN (
        DimDate[Date],
        DATE ( YEAR ( vActualsThru ), 1, 1 ),
        vActualsThru
    )
VAR vResult =
    CALCULATE (
        SUM ( FactTable[Amt] ),
        vDatesYTD,
        TREATAS ( CostPlan2, FactTable[Cost Plan] )
    )
RETURN
    vResult

Sample data:

Result:

8 Replies

DataInsights
Super User
3 years ago
Danminar,

Try this solution.

In Power Query, create a date column:

#date([Year], [Month], 1)

Create a date table with a relationship to the fact table:

Create measure:

Amount YTD = CALCULATE ( SUM ( FactTable[Amt] ), DATESYTD ( DimDate[Date] ) )

Result:

Danminar

Frequent Visitor

3 years ago

Thanks for the quick reply! Makes a lot of sense but what I really need (and not sure if possible) is to pick 2 cost plans and have Plan 2 base their "actuals" month off Plan 1, so I'm basically just picking Plan 1 in a slicer (in this case 4+8 so 4 months actuals current year) and then saying "return 4 months from current year in whatever cost plan I pick in second slicer".

So I had no problem with doing it for Full Year #s (both actual and forecast) but I just want it automated so user doesn't have to say how many months of actuals are in 1st plan to then display in 2nd measure/column. My slicers basically looks like this:

Danminar

Frequent Visitor

3 years ago

Last thing is no matter what plan is selected as "Plan 2" I want to return same amounts of "Current" months as Plan 1. Current Months as current year of selected plan so "2022 3+9" is 1st 3 months of 2022, "2023" Budget is 1st 3 months of 2023, etc.

I'm truly appreciative of any help anyone can supply! See below for condensed view of table:

Value	Plan Name	Month	Year	Period	Actual/Fcst	Curr/Out Year	Actuals Start	Trans Date
-1,977,843	2022 3+9	Jan	2022	1	Actual	Current	3/1/2022	1/1/2022
61,857	2022 3+9	Feb	2022	2	Actual	Current	3/1/2022	2/1/2022
1,410,276	2022 3+9	Feb	2022	2	Actual	Current	3/1/2022	2/1/2022
85,591	2022 3+9	Mar	2022	3	Actual	Current	3/1/2022	3/1/2022
848,368	2022 3+9	Mar	2022	3	Actual	Current	3/1/2022	3/1/2022
1,907,181	2022 3+9	Apr	2022	4	Fcst	Current	3/1/2022	4/1/2022
1,748,250	2022 3+9	May	2022	5	Fcst	Current	3/1/2022	5/1/2022
1,748,250	2022 3+9	Jun	2022	6	Fcst	Current	3/1/2022	6/1/2022
1,748,250	2022 3+9	Jul	2022	7	Fcst	Current	3/1/2022	7/1/2022
1,748,250	2022 3+9	Aug	2022	8	Fcst	Current	3/1/2022	8/1/2022
1,748,250	2022 3+9	Sep	2022	9	Fcst	Current	3/1/2022	9/1/2022
1,907,181	2022 3+9	Oct	2022	10	Fcst	Current	3/1/2022	10/1/2022
1,748,250	2022 3+9	Nov	2022	11	Fcst	Current	3/1/2022	11/1/2022
1,589,318	2022 3+9	Dec	2022	12	Fcst	Current	3/1/2022	12/1/2022

Actuals YTD	428,249

Danminar

Frequent Visitor

3 years ago

DataInsights

Let me 1st say I appreciate all your help but ran into a problem (my fault) when testing the data:

So the measure worked when comparing 2 plans that started with the same year such as:

Plan 1 = 2023 1+11

Plan 2 = P23

It took one month from both the 1st and the 2nd plans which is what I wanted. Where the problem occurs is when I am comparing something two plans with different different years such as:

Plan 1 = 2023 1+11

Plan 2 = 2022 1+11

What occurs in this case is that Plan 1 will return the expected result but plan 2 will return all amounts from 2022. This makes sense since the the variables are utilizing the column (Actuals Thru) to determine what to return.

What I really need is to be able to say "take the "Cost Plan Start Month from Plan 1 selected in slicer (Jan or "1" in this case) and apply that to Plan 2"

I have a separate slicer where user selects "Current", "Prior" or "Future" so when they use "Current", Plan 2023 1+11 should show dates of 1/1/23 only and if second plan is 2022 1+11 that should show 1/1/22 only. Basically a year over year comparison but user could actually want to compare a 2023 plan with a 2021 plan etc.

Here is an example:

Project Name	Value	Plan Name	Year	Period	Curr/Out Year	Cost Plan Month Start	Cost Plan Year Start	Actuals Thru	Date
Test	20,000	2022 1+11	2021	12	Prior	1	2022	1/1/2022	12/1/2021
Test	10,000	2022 1+11	2022	1	Current	1	2022	1/1/2022	1/1/2022
Test	10,000	2022 1+11	2022	11	Current	1	2022	1/1/2022	11/1/2022
Test	10,000	2022 1+11	2022	12	Current	1	2022	1/1/2022	12/1/2022
Test	25,000	2022 1+11	2023	12	Future	1	2022	1/1/2022	12/1/2023
Test	20,595	2023 1+11	2022	12	Prior	1	2023	1/1/2023	12/1/2022
Test	1,850	2023 1+11	2023	1	Current	1	2023	1/1/2023	1/1/2023
Test	1,964	2023 1+11	2023	12	Current	1	2023	1/1/2023	12/1/2023

Current Result:
	YTD
2023 1+11	1,850
2022 1+11	30,000

Expected Result:
2023 1+11	1,850
2022 1+11	10,000

I think the majority of the measure is good, just need to base the calculation off "Cost Plan Month Start" rather than "Actuals Thru"

DataInsights

Super User

3 years ago

Danminar,

Try creating a disconnected table for each plan (no relationships). You can do this in Power Query or DAX. Here are the calculated tables:

CostPlan1 = DISTINCT ( FactTable[Cost Plan] )

CostPlan2 = DISTINCT ( FactTable[Cost Plan] )

Create a slicer for each of these tables (Plan 1, Plan 2).

Measures:

Plan 1 Amount YTD = 
// get Actuals Thru for first plan and convert from text to date
VAR vActualsThru =
    CALCULATE (
        DATEVALUE ( SELECTEDVALUE ( FactTable[Actuals Thru] ) ),
        TREATAS ( CostPlan1, FactTable[Cost Plan] )
    )
VAR vDatesYTD =
    DATESBETWEEN (
        DimDate[Date],
        DATE ( YEAR ( vActualsThru ), 1, 1 ),
        vActualsThru
    )
VAR vResult =
    CALCULATE (
        SUM ( FactTable[Amt] ),
        vDatesYTD,
        TREATAS ( CostPlan1, FactTable[Cost Plan] )
    )
RETURN
    vResult

Plan 2 Amount YTD = 
// get Actuals Thru for first plan and convert from text to date
VAR vActualsThru =
    CALCULATE (
        DATEVALUE ( SELECTEDVALUE ( FactTable[Actuals Thru] ) ),
        TREATAS ( CostPlan1, FactTable[Cost Plan] )
    )
VAR vDatesYTD =
    DATESBETWEEN (
        DimDate[Date],
        DATE ( YEAR ( vActualsThru ), 1, 1 ),
        vActualsThru
    )
VAR vResult =
    CALCULATE (
        SUM ( FactTable[Amt] ),
        vDatesYTD,
        TREATAS ( CostPlan2, FactTable[Cost Plan] )
    )
RETURN
    vResult

Sample data:

Result:

Danminar
Frequent Visitor
3 years ago
Hi DataInsights

Thanks again for all your help but I'm having a problem with the 1st variable, I'm coming up with a DAX syntax error message.

Plan 1 Amount YTD =
VAR vActualsThru =
CALCULATE (
DATEVALUE ( SELECTEDVALUE ('All Versions'[Actuals Thru].[Date])) ,
TREATAS ('CostPlan1', 'All Versions'[Plan Name]))
I tried to change the actuals thru column name to text to see if that's the problem (it's a calculated column which may be the culprit?).

I ran your original suggestion thru DAX formatter and also came up with an error so I'm really confused. Any other suggestions?

Thanks so much!

Forum Discussion

Help with Dynamic Actual Amounts - Comparing 2 Forecast Plans

8 Replies

Related content

Forecasting with DAX

Compare Dynamic Columns in a Matrix

Dynamic Actuals with Forecast

Forecasting

Forecast total display