Forum Discussion

FGLacorte's avatar
FGLacorte
New Member
4 years ago

Excel Formula to DAX

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 ...
SpartaBI's avatar
SpartaBI
Icon for Community Champion rankCommunity Champion
4 years ago

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's avatar
FGLacorte
New Member
4 years ago

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's avatar
    SpartaBI
    Icon for Community Champion rankCommunity Champion
    4 years ago

    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