DAX Query to Return Text Value as of 1 Month Ago

0
votes

I am looking for some assistance, i am trying to create a DAX formula that will return a text value as of 1 month ago / the last entry date in a range of dates.

From the example table you see a list of codes in [JCS Grade] what i am trying to do is insert a new calculated column that will insert the previous months [JCS Grade] value, for example Employee Number 111 on Item Date 01 August 2019 has a [JCS Grade] = M2 and on 01 July they have a JCS Grade Value of M1 and i would like to return that previous months value in a calculated column [JCS1MonthAgo] but currently this is not working for me and i have tried the formula:

JCS1MonthAgo = CALCULATE(FIRSTNONBLANK(Sheet1[JCS Grade],DATEADD(Sheet1[Item Date],-1,MONTH)))

But this formula does not work as it only returns the current row value and not the prior months!

if anyone could spare a moment to assist me it would be greatly appreciated.

Many Thanks Picture of PowerBI Desktop Data Table as mentioned above

Chris

1
powerbidax

1 Answers

1
votes

You could use variables to do this calculation:

JCS1MonthAgo = 
        VAR EmpNo = Sheet1[Employee number]
        VAR ItemDate_Previous = EDATE(Sheet1[Item Date],-1)
RETURN CALCULATE(MIN(Sheet1[JCS Grade]),
                    FILTER(Sheet1,Sheet1[Employee number]=EmpNo),
                    FILTER(Sheet1,Sheet1[Item Date]=ItemDate_Previous))

The idea is to apply a filter to get the correct employee number and the date of the previous month. Hope this meets your requirements.