locked
Count Distinct Dates in the NEXTMONTH RRS feed

  • 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

Answers

  • 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 shawnebrown 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 shawnebrown 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