topic Re: Previous Month Calculation to Forecast in DAX Commands and Tips

Previous Month Calculation to Forecast

Anonymous — Wed, 13 Mar 2024 11:31:38 GMT

Hi all,

I have the below data:

Month	Headcount	Hires	Other Increase	Other Decrease
February	400		0	0
March		20	10	20
April		5	10	0
May		10	0	0

Problem statement:

I need to create a calculation that sums up the headcount, hires, increases and decreases for future months.

I want March to take February's headcount and add March's respective Hires, Other Increases and minus Other Decreases.

For example March would be 400 + 20 +10 -20 = 410

April would then take March's value (410) and perform the same calculation: 410 +5 +10 - 0 = 425

May would then take Aprils number and so on...

I am having trouble trying to get each month to take the calculated vale of the month before. Please help and thank you in advance.

Re: Previous Month Calculation to Forecast

JamesFR06 — Wed, 13 Mar 2024 12:50:56 GMT

Hi,

do you have a date table ?

Re: Previous Month Calculation to Forecast

Anonymous — Wed, 13 Mar 2024 12:57:12 GMT

Hi JamesFR06 - yes i do have a date table. The date field in the date table has a relationship with the 'headcount' table via the headcounts 'month' field

The headcount table is a row per unique employee , per month, and my headcount calculation is a countrows(headcount table) and then aggregated monthly via the date table relationship.

The hires, other increases, and other decreases fields are from other tables and also aggregated to a monthly level via relationships with the date table with their tables respective date column.

Let me know if that clarifies your question or if you need any further information.

Thanks

Re: Previous Month Calculation to Forecast

JamesFR06 — Wed, 13 Mar 2024 14:29:06 GMT

ib319961,

Thanks for the reply. And just to be sure you only have 1 year data in your model ?

Re: Previous Month Calculation to Forecast

Anonymous — Wed, 13 Mar 2024 14:43:43 GMT

JamesFR06 hi - i have data going to March 2025 in the date table so would like it to extend until the latest possible date in my date table, i'd only need to do the calculation for future months after my max month in my headcount table where i already have actuals. Thanks

Re: Previous Month Calculation to Forecast

JamesFR06 — Wed, 13 Mar 2024 14:48:07 GMT

The problem is if you link date and fact table by month it will not work. If you replace the month by a date it will work perfectly as longer as you want.

Can you replace the month by the date of this month with year of course ?

Re: Previous Month Calculation to Forecast

Anonymous — Wed, 13 Mar 2024 14:57:48 GMT

The below is what my data looks (same column order as table above) like with Month / Year coming from the date table.

I want March to pull February 2024 headcount (row above which i've cropped out in the screenshot due to data scrubbing) and add the three columns to the right of it. Eg if February 2024 was 400, it would be 400 +4 = 404. I'd then want April to use 404 as the base number and performn the calculation again, eg. 404 + 1 = 405.

Previous month calculations would mean that April would take the headcount for march which is currently 0 in my data, but i'd want it to take the calculated 404 from March looking up february. Let me know if that makes sense?

Re: Previous Month Calculation to Forecast

JamesFR06 — Wed, 13 Mar 2024 15:44:04 GMT

Here is the code. Your table has the name of Cumul head in my measure

Cumul Headcount =

VAR Periode =

SELECTEDVALUE ( 'Cumul Head'[Month] )

VAR positive =

CALCULATE (

SUM ( 'Cumul Head'[Headcount] ) + SUM ( 'Cumul Head'[Hires] )

+ SUM ( 'Cumul Head'[Other Increase] ),

'Cumul Head'[Month] <= periode,

ALL('Cumul Head')

)

VAR negative =

CALCULATE (

SUM ( 'Cumul Head'[Other Decrease] ),

'Cumul Head'[Month] <= periode,

all('Cumul Head')

)

VAR balance = positive - negative

RETURN

balance

Re: Previous Month Calculation to Forecast

Anonymous — Wed, 13 Mar 2024 16:38:45 GMT

hi JamesFR06 thanks for this, it's close but i also have a lot more months prior than february, i want the cumulative total to begin at the latest month for which i have actuals for (February only) - which would be the MAX date in my fact table, and then apply the calculation you gave above for the rest of the dates in my date table eg. to March 2025. I tried your calculation and it gave a cumulative total from the start to the end of my fact table. Thanks

Re: Previous Month Calculation to Forecast

JamesFR06 — Wed, 13 Mar 2024 17:23:45 GMT

Anonymous could you please post a copy of your date model and a row from your fact table ?

Thanks

Re: Previous Month Calculation to Forecast

Anonymous — Wed, 13 Mar 2024 17:36:16 GMT

My date table is as follows:

My fact table is a larger version of the below (500+ rows per month). My headcount calculation is

Report Month	Employee ID
January 2024	11
January 2024	12
January 2024	13
February 2024	11
February 2024	12
February 2024	13

My HEADCOUNT calculation is:

HEADCOUNT CURRENT MONTH =

VAR SelectedMonth = SELECTEDVALUE('table'[Report Month])

RETURN

CALCULATE (

COUNT ( 'table' [Employee ID] ),

'table'[Report Month] = SelectedMonth)

My date table extends to March 2025, while my fact table ends in February 2024 and updates at the end of each month. It has a relationship with the fact table via the Report month <- Date columns

In the above example, there's a headcount = 3 for february, i want to use this as the beginning point for the cumulative total, so march uses 3 as it's base and then adds planned hires, increases and decreases.

Once March comes into the fact table, this would be the new beginning of the cumulative total, and April would then use March's actual.

Thank you

Re: Previous Month Calculation to Forecast

JamesFR06 — Wed, 13 Mar 2024 20:32:23 GMT

OK so you have to do this :

1)Search the max date in your actuals table

var Atualsdate=calculate(max(actuals[Date])

2)Return the period in MMMM YY of this date

var MaxperiodMMYY=calculate(max(Calendar(MMMM YY),calendar[Date]=Actualsdate

3) make the cumul calculation

VAR Periode =

SELECTEDVALUE ( 'Cumul Head'[Month] )

VAR positive =

CALCULATE (

SUM ( 'Cumul Head'[Headcount] ) + SUM ( 'Cumul Head'[Hires] )

+ SUM ( 'Cumul Head'[Other Increase] ),

'Cumul Head'[Month] >= MaxperiodMMYY&&'Cumul Head'[Month] <= periode,

ALL('Cumul Head')

)

VAR negative =

CALCULATE (

SUM ( 'Cumul Head'[Other Decrease] ),

'Cumul Head'[Month] >= MaxperiodMMYY&&'Cumul Head'[Month] <= periode,

all('Cumul Head')

)

VAR balance = positive - negative

RETURN

balance

Re: Previous Month Calculation to Forecast

Anonymous — Thu, 14 Mar 2024 09:51:13 GMT

Apologies JamesFR06 - had to switch accounts due to firewall settings. I have tried your calculation but struggling to implement it.

Here is a dummy pbix https://drive.google.com/file/d/1BCnFd1q1f_k9a60EQIAm8Ndp5SuRoWu0/view?usp=sharing - i'm quite close with the 'closing' calculation but you can see the value for May is not calculating correctly which is causing the rest of the future months to be off. May should be 7 (Aprils value of 5 + 2 increase)

Re: Previous Month Calculation to Forecast

JamesFR06 — Thu, 14 Mar 2024 11:40:43 GMT

HI Loub123432

Here is the code :

Closing =
VAR dateMaxinFact =
CALCULATE (
MAX ( 'Fact Table'[Report Month ] ),
ALL ( 'Fact Table'[Report Month ] ),
REMOVEFILTERS ()
)
VAR YearDMF =
YEAR ( dateMaxinFact )
VAR MonthDMF =
MONTH ( dateMaxinFact )
VAR LastHeadcount =
CALCULATE (
MAX ( 'Fact Table'[Employee ID] ),
'Fact Table'[Report Month ] = dateMaxinFact,
REMOVEFILTERS ()
)
VAR BegNextPer =
EOMONTH ( dateMaxinFact, 0 ) + 1
VAR _Curr =
SELECTEDVALUE ( 'Date Table'[MonthInCalendar] )
VAR _Curr2 =
CALCULATE ( MIN ( 'Date Table'[Date] ), 'Date Table'[MonthInCalendar] = _Curr )
VAR EOMCurPer =
EOMONTH ( _Curr2, 0 )
VAR Headcount =
CALCULATE (
MAX ( 'Fact Table'[Employee ID] ) + COUNTROWS ( 'Other Increase and Decrease' )
)
VAR increaseOther =
CALCULATE (
COUNTROWS (
FILTER (
'Other Increase and Decrease',
'Other Increase and Decrease'[Type] = "Increase"
&& 'Other Increase and Decrease'[Date] >= BegNextPer
&& 'Other Increase and Decrease'[Date] <= EOMCurPer
)
),
REMOVEFILTERS ()
)
VAR increaseHiring =
CALCULATE (
COUNT ( Hiring[Name] ),
Hiring[Hiring Date] >= BegNextPer
&& 'Hiring'[Hiring Date] <= EOMCurPer,
REMOVEFILTERS ()
)
VAR Deacrease =
CALCULATE (
COUNTROWS (
FILTER (
'Other Increase and Decrease',
'Other Increase and Decrease'[Type] = "Decrease"
&& 'Other Increase and Decrease'[Date] >= BegNextPer
&& 'Other Increase and Decrease'[Date] <= EOMCurPer
)
),
REMOVEFILTERS ()
)
VAR result =
IF (
_Curr2 <= dateMaxinFact,
[HEADCOUNT],
LastHeadcount + increaseOther + increaseHiring - Deacrease
)
RETURN
result

Keep me in touch if it is ok for you

Re: Previous Month Calculation to Forecast

Anonymous — Thu, 14 Mar 2024 18:46:48 GMT

Wow... many thanks for this. With a couple of tweaks i've managed to replicate it in the real dashboard. Thank you! My final question - how would i allow the future month numbers to be impacted by the filters i apply from the fact table? If i apply a location filter, the months in my fact table will dynamically change, but all future months remain as a static number.

My calculation is as follows:

Closing FTE COMBINED =

VAR dateMaxinFact =

CALCULATE(

MAX('Forecast'[Date Month]),

ALL('Forecast'[Date Month]),

REMOVEFILTERS(),

'Forecast'[Type] = "STATIC"

)

VAR YearDMF =

YEAR ( dateMaxinFact )

VAR MonthDMF =

MONTH ( dateMaxinFact )

VAR LastHeadcount =

CALCULATE(

SUM('Forecast'[FTE]),

' Forecast'[Date Month] = dateMaxinFact,

REMOVEFILTERS(),

' Forecast'[TYPE] = "STATIC"

)

VAR BegNextPer =

EOMONTH ( dateMaxinFact, 0 ) + 1

VAR _Curr =

SELECTEDVALUE ( 'Date Table'[MonthInCalendar] )

VAR _Curr2 =

CALCULATE ( MIN ( 'Date Table'[Date] ), 'Date Table'[MonthInCalendar] = _Curr )

VAR EOMCurPer =

EOMONTH ( _Curr2, 0 )

VAR Headcount =

CALCULATE(

MAX('Forecast'[PID]) + COUNTROWS(' Forecast'),

'Forecast'[TYPE] = "CHANGES"

)

VAR increaseOther =

CALCULATE (

SUMX (

FILTER (

' Forecast',

'Forecast'[WORKFORCE IMPACT] = "Increase"

&& 'Forecast'[ACTION DATE] >= BegNextPer

&& 'Forecast'[ACTION DATE] <= EOMCurPer

'Forecast'[FTE]

REMOVEFILTERS ()

)

VAR increaseHiring =

CALCULATE (

COUNTROWS( 'RECRUITEMENT COMBINED' ),

'RECRUITEMENT COMBINED'[ Duty Date] >= BegNextPer

&& 'RECRUITEMENT COMBINED'[Duty Date] <= EOMCurPer,

REMOVEFILTERS ()

)

VAR Deacrease =

CALCULATE (

SUMX (

FILTER (

'Forecast',

' Forecast'[WORKFORCE IMPACT] = "Decrease"

&& ' Forecast'[ACTION DATE] >= BegNextPer

&& 'SIP Forecast'[ACTION DATE] <= EOMCurPer

' Forecast'[FTE]

REMOVEFILTERS ()

)

VAR result =

IF (

_Curr2 <= dateMaxinFact,

CALCULATE(

SUM('Forecast'[FTE]),

FILTER(' Forecast', 'Forecast'[TYPE] = "STATIC")

)

LastHeadcount + increaseOther - Deacrease

)

RETURN

result