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 December, as shown below.

 

Thanks,

4 Replies

  • 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

  • Anonymous's avatar
    Anonymous
    Not applicable
    4 years ago

    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