Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Anonymous
Not applicable

Projection Based off Calculated Field

I am doing five year projections my 2023 data is based off data that already exists to where my formula is the following:

 

2023 Estimate: =
CALCULATE( SUM('GL298A'[BUDGET-DTL] ),'GL298A'[FISCAL-YEAR] = 2022,'Accounts'[Object Category] = "Personnel")*-2 +
CALCULATE( SUM('GL298A'[BUDGET-DTL] ),'GL298A'[FISCAL-YEAR] = 2022,'Accounts'[Object Category] = "Contractual")*-2 +
CALCULATE( SUM('GL298A'[BUDGET-DTL] ),'GL298A'[FISCAL-YEAR] = 2022,'Accounts'[Object Category] = "Commodities")*-2+
CALCULATE( SUM('GL298A'[BUDGET-DTL] ),'GL298A'[FISCAL-YEAR] = 2022,'Accounts'[Object Category] = "Capital Outlay")*-2 +
CALCULATE( SUM('GL298A'[BUDGET-DTL] ),'GL298A'[FISCAL-YEAR] = 2022,'Accounts'[Object Category] = "Debt")*.5
 
For 2024 I cannot do this because I am using 2023 which is not in the form of a table.  How could I write this to where I can use mynewly calculated 2023 numbers but have each category multiplied by a different assumption.
1 ACCEPTED SOLUTION

That's why I put the Xxx2023 variables inside the measure and then referenced them from the Xxx2024 variables. Change the -3s I had to -1s and it should be close to what you're after.

View solution in original post

5 REPLIES 5
Whitewater100
Solution Sage
Solution Sage

Hi:

Can you just use the same measure for 2024 as 2023 but add and extra * _ at the end. If no change just multiply the 2023 forumla by 1. If 5% lower multiply by .95?

 

AlexisOlson
Super User
Super User

You'll probably have an easier time if you break things into variables.

2024 Estimate =
VAR Personnel2022 =
    CALCULATE (
        SUM ( 'GL298A'[BUDGET-DTL] ),
        'GL298A'[FISCAL-YEAR] = 2022,
        'Accounts'[Object Category] = "Personnel"
    )
VAR Contractual2022 =
    CALCULATE (
        SUM ( 'GL298A'[BUDGET-DTL] ),
        'GL298A'[FISCAL-YEAR] = 2022,
        'Accounts'[Object Category] = "Contractual"
    )
VAR Commodities2022 =
    CALCULATE (
        SUM ( 'GL298A'[BUDGET-DTL] ),
        'GL298A'[FISCAL-YEAR] = 2022,
        'Accounts'[Object Category] = "Commodities"
    )
VAR CapitalOutlay2022 =
    CALCULATE (
        SUM ( 'GL298A'[BUDGET-DTL] ),
        'GL298A'[FISCAL-YEAR] = 2022,
        'Accounts'[Object Category] = "Capital Outlay"
    )
VAR Debt2022 =
    CALCULATE (
        SUM ( 'GL298A'[BUDGET-DTL] ),
        'GL298A'[FISCAL-YEAR] = 2022,
        'Accounts'[Object Category] = "Debt"
    )
VAR Personnel2023     = -2 * Personnel2022
VAR Contractual2023   = -2 * Contractual2022
VAR Commodities2023   = -2 * Commodities2022 
VAR CapitalOutlay2023 = -2 * CapitalOutlay2022 
VAR Debt2023          = .5 * Debt2022
VAR Personnel2024     = -3 * Personnel2023
VAR Contractual2024   = -3 * Contractual2023
VAR Commodities2024   = -3 * Commodities2023
VAR CapitalOutlay2024 = -3 * CapitalOutlay2023
VAR Debt2024          = .4 * Debt2023
RETURN
	Personnel2024 + Contractual2024 + Commodities2024 + CapitalOutlay2024 + Debt2024

 

Anonymous
Not applicable

Ah I see.  Yes thank you.  I also did figured out another way kind of like the previous user stated by using a short one to have multiply by yearly assumption ie. 2024 would be 2022 numbers *1 = 2023 numbers*1 = 2024 numbers: below is what it is for personnel

 

2024 test =
VAR Personnel2022 =
CALCULATE (
SUM ( 'GL298A'[BUDGET-DTL] ),
'GL298A'[FISCAL-YEAR] = 2022,
'Accounts'[Object Category] = "Personnel"
)
VAR Personnel2023 = Personnel2022 * 1 * 1
RETURN
    Personnel2023
Anonymous
Not applicable

Okay this makes sense and it is much cleaner and easier, but I need one more step.  Based off using your formula above I need another calculation on the sidebar.  For example I would use:

 

2024 Estimate =
VAR Personnel2024 = -1 * 'Yearly Budget'[2023 Estimate:]
VAR Contractual2024 = -1 * 'Yearly Budget'[2023 Estimate:]
VAR Commodities2024 = -1 * 'Yearly Budget'[2023 Estimate:]
VAR CapitalOutlay2024 = -1 * 'Yearly Budget'[2023 Estimate:]
VAR Debt2024 = -1 * 'Yearly Budget'[2023 Estimate:] VAR OtherPayments2024 = -1 * 'Yearly Budget'[2023 Estimate:]
RETURN
    Personnel2024 + Contractual2024 + Commodities2024 + CapitalOutlay2024 + Debt2024 +OtherPayments2024 (and so on for following years)
 
However, when I do this the "yearly budget[2023 Estimate:] is not filtering off the codes such as debt contractual, etc.  Is it possible to write the second DAX with filtering by those expense types???

That's why I put the Xxx2023 variables inside the measure and then referenced them from the Xxx2024 variables. Change the -3s I had to -1s and it should be close to what you're after.

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors