topic Re: NEED HELP WITH LOOKUPVALUE in Calculated Column in DAX Commands and Tips

NEED HELP WITH LOOKUPVALUE in Calculated Column

HCC — Sat, 17 Dec 2022 18:45:49 GMT

Hi Everyone,

So I am trying to write a DAX Command to show MoM% Change for several metros I am Analyzing. The core calculation is the ((employment for the current metro)/(The employment for the month prior for the corresponding metro)-1). I am far more proficient in Excel and have used sumifs to solve this calculation, but I need help writing the formula for power BI. Because January's dates do not have a value before, I want to return a blank value.

The Table name is 'Employment Analysis', see sample table below:

Here is the formula I tried to write:

Metro MoM% Change = IFERROR(IF(ISBLANK('Employment Analysis'[Employment]),BLANK(),'Employment Analysis'[Employment]/LOOKUPVALUE('Employment Analysis'[Employment],'Employment Analysis'[Date],EDATE('Employment Analysis'[Date],-1),'Employment Analysis'[Employment],VALUE('Employment Analysis'[Area]))-1),BLANK())

Any help is much appreciated! Last item I need to calculate on my report!

Re: NEED HELP WITH LOOKUPVALUE in Calculated Column

FreemanZ — Sat, 17 Dec 2022 23:53:23 GMT

hi HCC

try like:

MoM％ :=

VAR _m = [Metro]

VAR _d = [Date]

VAR _e =

MINX(

FILTER(

tbl,

_m = [Metro]

&&YEAR(_d)=YEAR([Date])

&&MONTH(_d)=MONTH([Date])-1

[Employment]

)

RETURN

FORMAT(DIVIDE([Employment] - _e, _e), "0.0%")

Re: NEED HELP WITH LOOKUPVALUE in Calculated Column

Anonymous — Mon, 19 Dec 2022 02:58:53 GMT

Hi HCC ,

I think you can try this code to create a calculated column.

MoM％ Change = VAR _PerviousDate = CALCULATE ( SUM ( 'Employment Analysis'[Employment] ), FILTER ( ALLEXCEPT ( 'Employment Analysis', 'Employment Analysis'[Metro] ), 'Employment Analysis'[Date] = EOMONTH ( EARLIER ( 'Employment Analysis'[Date] ), -2 ) + 1 ) ) VAR _CurrentDate = CALCULATE ( SUM ( 'Employment Analysis'[Employment] ) ) VAR _Percentage = DIVIDE ( _CurrentDate, _PerviousDate ) - 1 RETURN IF ( _PerviousDate = BLANK (), BLANK (), _Percentage )

Result is as below.

Best Regards,
Rico Zhou

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.