Get next month based on status

[kannanAhammed]

Hi All,

I would like to get next month from a column "Modified" (which is of date data type). if the status column is "Completed".

something like this.

MeasureNextmonth = if (status == 'Completed')

{

NEXTMONTH('Tablename'[Modified])

}

also in dax function i can only access measure colums. How do i write expressions based on default columns loaded during transformation stage

[amitchandak]

@kannanAhammed , not very clear, Measure would be like

if (max(status) = 'Completed',NEXTMONTH('Tablename'[Modified]),blank())

[nvprasad]

Hi,

Try below measrue

Measure Nextmonth = IF (Status = "Modified",  EOMONTH(H10,0)+1 , EOMONTH(H10,-1)+1)

This gives next month first date by defualt and you can use format function to get desired format.

Appreciate a Kudos! 🙂
If this helps and resolves the issue, please mark it as a Solution! 🙂

Regards,
N V Durga Prasad

[kannanAhammed]

Thank you,

Measure Nextmonth = IF (Status = "Modified",  EOMONTH(H10,0)+1 , EOMONTH(H10,-1)+1)

But i am not able to type "Status" its only accepting Measure column not other colum to be added in the IF expression.

[v-lili6-msft · Accepted solution]

HI  @kannanAhammed 

This formula will work for a new column, if you want to create a measure, you need to adjust it as below:

Measure Nextmonth = IF (MAX(Tablename[status]) = "Modified", EOMONTH(MAX(Tablename[Modified]),0)+1 , EOMONTH(MAX(Tablename[Modified]),-1)+1)

https://www.sqlbi.com/articles/calculated-columns-and-measures-in-dax/

https://radacad.com/measure-vs-calculated-column-the-mysterious-question-not

Regards,

Lin

[kannanAhammed]

Thank you, it worked

few doubts,

1) Max() return the largest value. How is this working in this expression. 

2) when i tried using existing column in expression the fornula always shows error. but when i use it inside max column it worked.

please excuse my English as its not my first language.

[v-lili6-msft]

hi  @kannanAhammed 

 Measure is a summary value by its row context in a visual, so MAX/MIN has the same logic.

https://www.sqlbi.com/articles/calculated-columns-and-measures-in-dax/

Regards,

Lin