# Count Distinct Dates in the NEXTMONTH

• ### Question

•  Hi- First, I want to thank everyone in advance for all that I have learned in various forums and postings as well as Marco Russo & Alberto Ferrari for such a wonderful book. I am such a newbie at PowerPivot, but I am determined to learn this thing and use it! For the life of me, I can not figure this out: I have a DATA table that holds both a PLAN & ACTUALS unioned together. The Actuals are sales by day and the plan has as a date the first day of the month. I am using a DATE TABLE. In my report i creating MEASURES for the CURRENT MONTH, PRIOR YEAR as well as for the next month. The Next month is the forward month of what is selected. I am trying COUNT the number of unique dates of the NEXTMONTH and can't seem to figure out how to get that. The reason is that I want to see how many days we are into the month of shipping days and then divide the plan accordingly to come up with a idea of how much of the plan has been achieved. I sure hope this all makes sense..

Saturday, April 2, 2011 2:00 AM

• Hi shawnebrown,

There is a NEXTMONTH function in DAX, which gives you the dates from the next month. You can count these like this:

=COUNTROWS(NEXTMONTH('Date'[DateDay]))

Here Date is the Date table and DateDay is a column, which is unique for each day - e.g. 01/01/2011 (Jan 2011), 01/02/2011 (Feb 2011), etc.

The BOL reference to Time intelligence functions in DAX is here: http://msdn.microsoft.com/en-us/library/ee634763.aspx

Boyan Penev --- http://www.bp-msbi.com
• Marked as answer by Monday, April 4, 2011 3:05 AM
Saturday, April 2, 2011 1:26 PM

### All replies

• Hi shawnebrown,

There is a NEXTMONTH function in DAX, which gives you the dates from the next month. You can count these like this:

=COUNTROWS(NEXTMONTH('Date'[DateDay]))

Here Date is the Date table and DateDay is a column, which is unique for each day - e.g. 01/01/2011 (Jan 2011), 01/02/2011 (Feb 2011), etc.

The BOL reference to Time intelligence functions in DAX is here: http://msdn.microsoft.com/en-us/library/ee634763.aspx

Boyan Penev --- http://www.bp-msbi.com
• Marked as answer by Monday, April 4, 2011 3:05 AM
Saturday, April 2, 2011 1:26 PM
• Boyan-

THANKYOU!! This worked!!!! :)

Can't thank you enough..

Monday, April 4, 2011 3:04 AM