locked
Calculated column - how to find the first date of a month RRS feed

  • Question

  • Have a calendar table, date column is defined.  How do I find the first date of a month, i.e. 1.1.2015, 2.1.2015, 3.1.2015?   Date column is Data type: Date, and formatted as Date time.  Formatting without time still produces the error.  STARTOFMONTH produces the following error "Column 'Date Full' cannot be found or may not be used in this expression".


    Thanks in advance,

    Mike

    Thursday, February 5, 2015 11:06 PM

Answers

  • Hi Mike,

    most Power Pivot Designers will have a/several calender table/s in their toolbox which they simply add to the model as needed (create or filter relevant timespan before, add or delete specific columns). This is normally quicker and safer than deriving it from the source data (you must ensure, that the calendar/date table covers all days existing, otherwise the time intelligence functions will not work properly).

    These would normally include separate columns for the numbers of year, month and day. With these, you could create the first day of the month like this : :=DATE([Year], [MonthNo], 01). (If this field by then isn't contained in your personal date tables :-)

    I prefer to create my date tables in Excel, here's a good site showing the formulas you need: http://www.powerpivot-info.com/post/208-q-how-can-i-create-calendar-datetime-table-for-powerpivot-if-i-do-not-have-any-source-for-that

    However, it can be done in Power Pivot as well - here you find an article that describes how to split up your date field into the respective columns containing the numbers : http://www.nimblelearn.com/Blog/Post/14/Using-DAX-to-Split-Delimited-Text-into-Columns


    Imke


    Sunday, February 8, 2015 7:45 AM
    Answerer

All replies

  • Hopefully this helps you out in parsing the string and turning it into a DateTime and then finding the start and end day of a month.

                string datestring = "1.1.2015";
                DateTime temp;
                DateTime.TryParseExact(datestring, "M.d.yyyy", CultureInfo.InvariantCulture, DateTimeStyles.AssumeLocal, out temp);
                var firstDayOfMonth = new DateTime(temp.Year, temp.Month, 1);
                var lastDayOfMonth = firstDayOfMonth.AddMonths(1).AddDays(-1);

    Thursday, February 5, 2015 11:11 PM
  • Wow.  This is what I put in the calendar table of a Power Pivot data model?
    Friday, February 6, 2015 12:59 AM
  • Trying my best to learn Power Pivot - guess I didn't ask this correctly.  In my calendar table I have a date column listing all the dates from 1.1.2010 through 12.31.2024.  I'd like to create a column in this table that for every date in the month of say Jan 2010 the row shows 1.1.2010, for Feb 2010 they show 2.1.2010.  StartofMonth and FirstDate don't seem to work in a calculated column.
    Saturday, February 7, 2015 9:55 PM
  • Hi Mike,

    most Power Pivot Designers will have a/several calender table/s in their toolbox which they simply add to the model as needed (create or filter relevant timespan before, add or delete specific columns). This is normally quicker and safer than deriving it from the source data (you must ensure, that the calendar/date table covers all days existing, otherwise the time intelligence functions will not work properly).

    These would normally include separate columns for the numbers of year, month and day. With these, you could create the first day of the month like this : :=DATE([Year], [MonthNo], 01). (If this field by then isn't contained in your personal date tables :-)

    I prefer to create my date tables in Excel, here's a good site showing the formulas you need: http://www.powerpivot-info.com/post/208-q-how-can-i-create-calendar-datetime-table-for-powerpivot-if-i-do-not-have-any-source-for-that

    However, it can be done in Power Pivot as well - here you find an article that describes how to split up your date field into the respective columns containing the numbers : http://www.nimblelearn.com/Blog/Post/14/Using-DAX-to-Split-Delimited-Text-into-Columns


    Imke


    Sunday, February 8, 2015 7:45 AM
    Answerer
  • Thank you so much Imke.  Power-Pivot can provide such simple solutions.  I had the calendar table set up so it didn't take much once you pointed me in the right direction.  Have done the Power Pivot course and read the book, now I have to comprehend it all - not my strongest area :).

    Thanks again,

    Mike

    Sunday, February 8, 2015 9:26 PM