Creating a conditional column

[deanbland]

Hi, 

I am trying to create a column that presents "True" or "False" depending on the date. I am wanting the column to look at 'Report Date', assess whether it is the current month or last month, and then present a value in the 'Previous Month RAG'. 

If possible, It would be beneficial to show the RAG status from the previous month in the 'Previous Month RAG' rather than just 'True' or 'False' (a copy and paste if you will). IF you look below, I have tried to do this but It is currently presenting values for everything and not just the previous month. 

The sample data is below along with the formula that I have used. 

Previous Month RAG = CALCULATE(FIRSTNONBLANK('Open_Activities_DAI'[RAG],TRUE()),FILTER(ALLEXCEPT('Open_Activities_DAI','OPEN_ACTIVITIES_DAI'[RAG]),MONTH('Open_Activities_DAI'[Report date])=MONTH(TODAY())-1)) 

[v-eqin-msft]

Hi @deanbland ,

Could you tell me if your problem has been solved? If it is, kindly Accept it as the solution. More people will benefit from it. Or you are still confused about it, please provide me with more details about your table and your problem or share me with your pbix file from your Onedrive for Business.

Best Regards,
Eyelyn Qin

[v-eqin-msft]

Hi @speedramps ,

Please try this:

Measure =
CALCULATE (
    MAX ( 'Open_Activities_DAI'[RAG] ),
    PREVIOUSMONTH ( 'Open_Activities_DAI'[Report Date] )
)

 

Did I answer your question ? Please mark my reply as solution. Thank you very much.
If not, please upload some insensitive data samples and expected output.

Best Regards,
Eyelyn Qin

[speedramps]

Please consider this solution and click accept solution to leave kudos

Try using a calendar table with a date and month offset column
Eg 0 = this month, -1 = last month, -2 =pervious month and +1 next month

Then create a one to many relationship between the calendar[date] and  RAGS(date)

Then create the following dax measures

RAG Last Month =

CALCULATE(SUM(RAGS[RAG]),

'Calendar'[MonthOffset]=-1)

RAG This Month =

CALCULATE(SUM(RAGS[RAG]),

'Calendar'[MonthOffset]=0)

Click here for PBIX example 

[amitchandak]

@deanbland , a New column like

maxx(filter(table, 'OPEN_ACTIVITIES_DAI'[Report Date] =dateadd(earlier('OPEN_ACTIVITIES_DAI'[Report Date]),-1,month) ),'OPEN_ACTIVITIES_DAI'[RAG])

or

maxx(filter(table, 'OPEN_ACTIVITIES_DAI'[Report Date] =dateadd(earlier('OPEN_ACTIVITIES_DAI'[Report Date]),-1,month)
&& 'OPEN_ACTIVITIES_DAI'[RDAI Title] =earlier('OPEN_ACTIVITIES_DAI'[RDAI Title] ) ),'OPEN_ACTIVITIES_DAI'[RAG])