Forum Discussion

Danminar's avatar
Danminar
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 Numb...
Need Help
  • DataInsights's avatar
    DataInsights
    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's avatar
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's avatar
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:

 

ValuePlan NameMonthYearPeriodActual/FcstCurr/Out YearActuals StartTrans Date
-1,977,8432022 3+9Jan20221ActualCurrent3/1/20221/1/2022
61,8572022 3+9Feb20222ActualCurrent3/1/20222/1/2022
1,410,2762022 3+9Feb20222ActualCurrent3/1/20222/1/2022
85,5912022 3+9Mar20223ActualCurrent3/1/20223/1/2022
848,3682022 3+9Mar20223ActualCurrent3/1/20223/1/2022
1,907,1812022 3+9Apr20224FcstCurrent3/1/20224/1/2022
1,748,2502022 3+9May20225FcstCurrent3/1/20225/1/2022
1,748,2502022 3+9Jun20226FcstCurrent3/1/20226/1/2022
1,748,2502022 3+9Jul20227FcstCurrent3/1/20227/1/2022
1,748,2502022 3+9Aug20228FcstCurrent3/1/20228/1/2022
1,748,2502022 3+9Sep20229FcstCurrent3/1/20229/1/2022
1,907,1812022 3+9Oct202210FcstCurrent3/1/202210/1/2022
1,748,2502022 3+9Nov202211FcstCurrent3/1/202211/1/2022
1,589,3182022 3+9Dec202212FcstCurrent3/1/202212/1/2022
         
Actuals YTD       428,249      
  • Danminar's avatar
    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 NameValuePlan NameYearPeriodCurr/Out YearCost Plan Month StartCost Plan Year StartActuals ThruDate
    Test20,0002022 1+11202112Prior120221/1/202212/1/2021
    Test10,0002022 1+1120221Current120221/1/20221/1/2022
    Test10,0002022 1+11202211Current120221/1/202211/1/2022
    Test10,0002022 1+11202212Current120221/1/202212/1/2022
    Test25,0002022 1+11202312Future120221/1/202212/1/2023
    Test20,5952023 1+11202212Prior120231/1/202312/1/2022
    Test1,8502023 1+1120231Current120231/1/20231/1/2023
    Test1,9642023 1+11202312Current120231/1/202312/1/2023
              
    Current Result:        
     YTD        
    2023 1+111,850        
    2022 1+1130,000

     

    		       
              
    Expected Result:        
    2023 1+111,850        
    2022 1+1110,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"