How to identify last activity in month

[guzal19971]

I face the problem when I need to identify the last retail audit. Right now in my dataset we have a column named Last retail audit, which specifies last retail audit over the whole period of time. In the attached picture, you can find an example based on one account code. The last retail audit over 2022 year was made on Dec 6, and Last retail audit column gives value as true. However, in my case I need to show the last retail audit in the month during the year. For example, in one account code in November there were two retail audits, and in October- three retail audit and in September only one audit. I need Last retail audit to show value as true on the last retail audit made in Nov based on recent date in November, and last made in October. As a result, there can be two or more last retail audit flag for one account code or only one if there were only one retail audit in Month. In attachment you can find a picture of expected result. Can you provide any suggestion on how to solve this issue?

[Ashish_Mathur]

Hi,

Write these calculated column formulas

Month and Year = FORMAT(Data[Actual end date],"mm-yy")

Last retail audit in month = Data[Actual end date] = calculate(max(Data[Actual end date]),filter(Data,Data[Code]=earlier(Data[Code])&&data[Month and Year]=earlier(data[Month and Year])))

Hope this helps.

[amitchandak]

@guzal19971 , Create a new column

=

Var _month = eomonth([Actual end Date],0)

return

if(maxx(filter( Table, [Code] = earlier([Code]) && eomonth([Actual end Date],0) = _month ) , [Actual end Date]) =[Actual end Date], "Y", "N")

[guzal19971]

Hello, thank you for your reply.

I've tried to implement this approach and it gives result saying that "The function MAXX can not work with values of type Boolean"

I tried to change and use MAXA instead of MAXX, it says that only one column is accepted as argument for this function. 

last_month = Var _month = eomonth(SELECTEDVALUE(Visit[Actual end date]),0)

var _maxa= MAXX(filter( Account,Account[_SK_ACCOUNT] = earlier('Retail Audit - Fact'[_SK_ACCOUNT]) && eomonth(SELECTEDVALUE(Visit[Actual end date]),0) = _month), SELECTEDVALUE(Visit[Actual end date]) =SELECTEDVALUE(Visit[Actual end date]))

return

if(_maxa  , "Y", "N")

This is the way how I am trying to make it now, and result is The function MAXX can not work with values of type Boolean

Maybe there is other any kind of function that can take into consideration both conditions?

Thank you very much for your great job.

Best regards, Gyuzal