topic Re: NextMonth and Next Quarter () in DAX Commands and Tips

NextMonth and Next Quarter ()

senthil_venkat — Tue, 04 Aug 2020 21:57:51 GMT

Hi All, I have a excel table which has due date. I am trying to identify the which all the rows that will be due in next month using the NextMonth(). But I am getting below error message. Can someone help me to fix this Nextmonth () ? Or Guide me how to calculate the rows has a due date as next month.

Code	Filing Due Date	NextMonth Due	Next Qtr Due
99M9	31-Jul-20	N	N
1065	15-Jul-20	N	N
1065	15-Aug-20	Y	Y
5945	31-Mar-19	N	N
3675	31-May-20	N	N
6465	31-Mar-19	N	N
3505	30-Sep-20	N	Y
5055	30-Sep-20	N	Y
1735	30-Sep-20	N	Y
3815	31-Aug-20	Y	Y
3815	31-Aug-20	Y	Y
2885	31-May-20	N	N

Re: NextMonth and Next Quarter ()

lbendlin — Wed, 05 Aug 2020 00:48:37 GMT

NEXTMONTH() works a bit different from what you expect.

https://docs.microsoft.com/en-us/dax/nextmonth-function-dax

Returns a table that contains a column of all dates from the next month, based on the first date in the dates column in the current context.

You need to test if your Filing Due Date is in the NEXTMONTH() table.

DueNextMonth := 'EFSS Entities'[Filing Due Date] IN NEXTMONTH(Dates[date])

Re: NextMonth and Next Quarter ()

senthil_venkat — Wed, 05 Aug 2020 01:14:03 GMT

Hi,

I am not seeing the expected results. I am getting just "False" for every row. Include July rows

I did try using DateAdd function, but many rows are having blank values. Any thoughts how to fix?

DueNextQtr = (DateAdd('EFSS Entities'[Filing Due Date],1,QUARTER))

Re: NextMonth and Next Quarter ()

lbendlin — Wed, 05 Aug 2020 01:17:34 GMT

Did you link in your Dates table? Or do you want to compare to "Today" only?

Re: NextMonth and Next Quarter ()

senthil_venkat — Wed, 05 Aug 2020 01:34:29 GMT

I am not using any dates table. Just need to compare the filing date to Today's date and list out which are the rows that will be due next month and next quarter.

Re: NextMonth and Next Quarter ()

amitchandak — Wed, 05 Aug 2020 03:14:02 GMT

senthil_venkat , There is some logic problem. But please find my file after the signature. Check for this qtr, next qtr and next month. Also, if needed, date>today in the next month formula just like I added in this qtr.