Adding new column that shows This Month or Next Month

[sanman]

I have a table with date column MYDATE. I want to create a new column that shows "This Month", "Next Month" or ""  depending on the MYDATE field if it's This month, next month or neither.

This should be easy but have not been able to figure it out.

[V-lianl-msft]

Hi @sanman ,

If you are using direct queries, the time intelligence function has some limitations

https://docs.microsoft.com/en-us/power-bi/connect-data/desktop-directquery-about#modeling-limitation... 

Please try creating new column like below:

Months = 
VAR Today_ = 
DATE ( YEAR ( NOW () ), MONTH ( NOW () ), 1 )
return
IF (
    AND ( [Date]>=DATE ( YEAR ( NOW () ), MONTH ( NOW () )-1, 1 ) ,[Date]<Today_ ),
    "Last Month";
    IF (
        AND ( [Date]>=DATE ( YEAR ( NOW () ), MONTH ( NOW () ), 1 ),[Date]<DATE ( YEAR ( NOW () ), MONTH ( NOW () )+1, 1 )  ),
        "This Month",
		IF (
        	AND ( [Date]>=DATE ( YEAR ( NOW () ), MONTH ( NOW () )+1, 1 ),[Date]<DATE ( YEAR ( NOW () ), MONTH ( NOW () )+2, 1 ) ),
        	"Next Month",
        	IF ( Today_ = 0, "null" )
    )
))

In case of import mode, amitchandak 's answer is also a good solution.

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

[amitchandak · Accepted solution]

@sanman ,  A new column

Month Type = Switch( True(),

eomonth([Date],0) = eomonth(Today(),1),"Next Month" ,
eomonth([Date],0) = eomonth(Today(),-1),"Last Month" ,
eomonth([Date],0)= eomonth(Today(),0),"This Month" ,
Format([Date],"MMM-YYYY")
)

Or time intelligence and date tables measures like

MTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESMTD('Date'[Date]))
last MTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESMTD(dateadd('Date'[Date],-1,MONTH)))
last month Sales = CALCULATE(SUM(Sales[Sales Amount]),previousmonth('Date'[Date]))
next month Sales = CALCULATE(SUM(Sales[Sales Amount]),nextmonth('Date'[Date]))

Power BI — Month on Month with or Without Time Intelligence
https://medium.com/@amitchandak.1978/power-bi-mtd-questions-time-intelligence-3-5-64b0b4a4090e
https://www.youtube.com/watch?v=6LUBbvcxtKA