Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
6 years ago
Solved

Get next month based on status

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 == 'Comp...
General Comment
Need Help
Tips and Tricks
nvprasad's avatar
nvprasad
Icon for Solution Sage rankSolution Sage
6 years ago

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

  • Anonymous's avatar
    Anonymous
    Not applicable
    6 years ago

    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's avatar
      v-lili6-msft
      Icon for Community Support rankCommunity Support
      6 years ago

      HI  Anonymous 

      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

      • Anonymous's avatar
        Anonymous
        Not applicable
        6 years ago

        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.