DAX Logical Issue

[NimaiAhluwalia]

Hello I have a usecase which i am not sure how to create a DAX for, need a hand!!

If the above is my table and i want to build a calculated column as previous, which takes the priormonth value of the project (the value is status column here for me) and displays the result in the previous column as shown above!!

Problem - I can use Time intelligence function previousmonth but how it will filter the exact row for me, example, for the Project 1 - Nov, if i use Prreviousmont it will caputure  Project 1 - oct and  Project 2 - Oct (as they will be displayed due to having a date value of Oct), But i only want to get the data for  Project 1 - Oct for  Project 1 - Nov.

Please let me know if you want some more clarificaition or please suggest if we can do it by measure as well, i am open to all suggestions thanks

@amitchandak 

@Greg_Deckler 

[v-zhangti · Accepted solution]

Hi, @NimaiAhluwalia 

You can try the following methods.

Column = 
Var _Previous=MAXX(FILTER('Table',[Project ID]=EARLIER('Table'[Project ID])&&[Date]<EARLIER('Table'[Date])),[Date])
Return
CALCULATE(MAX('Table'[Status]),FILTER('Table',[Project ID]=EARLIER('Table'[Project ID])&&[Date]=_Previous))

Measure = 
Var _Previous=MAXX(FILTER(ALL('Table'),[Project ID]=SELECTEDVALUE('Table'[Project ID])&&[Date]<SELECTEDVALUE('Table'[Date])),[Date])
Return
CALCULATE(MAX('Table'[Status]),FILTER(ALL('Table'),[Project ID]=SELECTEDVALUE('Table'[Project ID])&&[Date]=_Previous))

Is this the result you expect? Please see the attached document.

Best Regards,

Community Support Team _Charlotte

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

[Anonymous]

Try the following:

CALCULATE(SELECTEDVALUE('YourTable'[Status]), FILTER(ALL('YourTable'),

                                                    'YourTable'[Project ID] = SELECTEDVALUE('YourTable'[Project ID])

                                                    && MONTH(SELECTEDVALUE('YourTable'[Date])) = MONTH('YourTable'[Date]) - 1

                                                    && YEAR(SELECTEDVALUE('YourTable'[Date])) = YEAR('YourTable[Date]))

)

This should effectively remove all of the row level filter context, then reapply it but setting the previous month as the value to find the status for. Hope this helps, let me know if you have any issues or questions.