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
Chris