Syntax
Tutorial with example
I am working on a calculation that requires to count the months remaining on the year for a fiscal calendar and I am sharing some possible solutions with you. As always, there is more that one way to Rome, so feel free to share your alternative solutions! Keynotes: 1. Calculate current month in standard calendar 2. Wrong way to calculate on fiscal calendar (MONTH; ENDOFMONTH) 3. Calculate remaining months manually 4. Do the calculation grabbing the value on the table.
Download example file:
Download Power BI file: here.
Remarks
In contrast to Microsoft Excel, which stores dates as serial numbers, DAX uses a datetime format when working with dates. You can enter the date used as argument to the MONTH function by typing an accepted datetime format, by providing a reference to a column that contains dates, or by using an expression that returns a date.
Values returned by the YEAR, MONTH and DAY functions will be Gregorian values regardless of the display format for the supplied date value. For example, if the display format of the supplied date is Hijri, the returned values for the YEAR, MONTH and DAY functions will be values associated with the equivalent Gregorian date.
When the date argument is a text representation of the date, the function uses the locale and date time settings of the client computer to understand the text value in order to perform the conversion. If the current date time settings represent a date in the format of Month/Day/Year, then the following string “1/8/2009” is interpreted as a datetime value equivalent to January 8th of 2009, and the function yields a result of 1. However, if the current date time settings represent a date in the format of Day/Month/Year, then the same string would be interpreted as a datetime value equivalent to August 1st of 2009, and the function yields a result of 8.
If the text representation of the date cannot be correctly converted to a datetime value, the function returns an error.
Let us know your thoughts...