topic Re: Excel Formula to DAX in DAX Commands and Tips

Excel Formula to DAX

FGLacorte — Mon, 25 Jul 2022 10:00:23 GMT

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,

Re: Excel Formula to DAX

SpartaBI — Mon, 25 Jul 2022 10:08:50 GMT

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?

Re: Excel Formula to DAX

FGLacorte — Mon, 25 Jul 2022 10:35:14 GMT

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.

Re: Excel Formula to DAX

SpartaBI — Mon, 25 Jul 2022 10:55:46 GMT

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

Re: Excel Formula to DAX

Anonymous — Mon, 08 Aug 2022 11:18:58 GMT

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