NEED HELP WITH LOOKUPVALUE in Calculated Column

[HCC]

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!

[Anonymous · Accepted solution]

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.

[FreemanZ]

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%")