topic Re: Projection Based off Calculated Field in DAX Commands and Tips

Projection Based off Calculated Field

Anonymous — Mon, 28 Feb 2022 21:39:10 GMT

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.

Re: Projection Based off Calculated Field

AlexisOlson — Mon, 28 Feb 2022 21:59:03 GMT

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

Re: Projection Based off Calculated Field

Whitewater100 — Mon, 28 Feb 2022 21:59:19 GMT

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?

Re: Projection Based off Calculated Field

Anonymous — Tue, 01 Mar 2022 14:44:45 GMT

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???

Re: Projection Based off Calculated Field

AlexisOlson — Tue, 01 Mar 2022 15:54:07 GMT

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.

Re: Projection Based off Calculated Field

Anonymous — Tue, 01 Mar 2022 16:02:12 GMT

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