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

     

     


    In case it answered your question, please accept the solution to help other members find it. Appreciate Your Kudos.


    SpartaBI Logo

    Visit SpartaBI website   Visit SpartaBI Linkdin   Visit SpartaBI Facebook

    Showcase Report – Contoso By SpartaBI