Excel Formula to DAX

[FGLacorte]

Hi all, need assistance how to do this excel formula to DAX. Basically i want to forecast my headcount until year-end by adding the latest actual headcount (is July) on number to hire from August to December, as shown below.

Thanks,

[Anonymous]

Hi @FGLacorte ,

Please new a calculated column:

Forecast =
VAR _month =
    CALCULATE (
        MAX ( 'Table'[Date] ),
        FILTER ( 'Table', 'Table'[Actual Headcount] <> BLANK () )
    )
VAR _actual_max =
    CALCULATE (
        MAX ( 'Table'[Actual Headcount] ),
        FILTER ( ALL ( 'Table' ), 'Table'[Date] = _month )
    )
VAR _result =
    IF (
        'Table'[Actual Headcount] = BLANK (),
        SUMX (
            FILTER ( 'Table', 'Table'[Date] <= EARLIER ( 'Table'[Date] ) ),
            'Table'[To Hire]
        ) + _actual_max
    )
RETURN
    _result

Best Regards,
Gao
Community Support Team

If there is any post helps, then please consider Accept it as the solution to help the other members find it more quickly. If I misunderstand your needs or you still have problems on it, please feel free to let us know. Thanks a lot!

How to get your questions answered quickly -- How to provide sample data

[SpartaBI]

@FGLacorte it depends how you want to present it, in a calculated column or in measure in a visual. Need more details on your specific scenario.
Can you share a sample file and write how and where you want to show the result?

[FGLacorte]

I have a matrix table for that, as below. But i am failing to get the correct formula in forecast. All of them are measures.

[SpartaBI]

@FGLacorte it will be something like that but to give you the exact syntax either you share the file or do a zoom with me:

Forecast = 
VAR _current_date = MAX('Table'[Date])
VAR _latest_actual_date = MAXX(FILTER(ALL('Table'), 'Table'[Actual Headcount] <> BLANK()),'Table'[Date])
VAR _latest_actual = CALCULATE(SUM('Table'[Actual Headcount]), 'Table'[Date] = _latest_actual_date, REMOVEFILTERS('Table'))
VAR _result = 
	SWITCH(
		TRUE(),
		_current_date <= _latest_actual_date, CALCULATE(SUM('Table'[Actual Headcount])),
		_latest_actual + CALCULATE(SUM('Table'[To Hire))
	)
RETURN
	_result

      

Showcase Report – Contoso By SpartaBI