Forum Discussion
Problem with date construction using DATE function
- Anonymous9 years ago
Hi jblackshear,
>>Measures using a date that was created by programmatic manipulation of a real date cause an error, an error specifically referring to an invalid DATE value, when used in a chart (but no syntax errors in creating the measure).
"Date" function not available on mutiple date, power bi will show you the date function error "date too small or too large". So date function only works on single date calculate, if you want to filter on mutiple date, you can try to use SAMEPERIODLASTYEAR, DATEADD functions. (dateadd also works on your formula)
Regards,
Xiaoxin Sheng
Hi jblackshear,
Can you share some sample file to test? I test on some sample data without any issue.
Regards,
Xiaoxin Sheng
- jblackshear9 years ago
Advocate III
I'm not sure if there's a better way to share. Here is a link to a sample file in Dropbox.
The measures named "xxx Current x" are intended to always use the LatestPostingDate from the data, and always ignore any date filters that would affect the YTD measures.
LatestPostingDate gets LastDate(GLPostingDate) from the Sales Transactions table, disregarding any date filters or slicers. It seems to work fine.
LatestPostingDateLY constructs a new date from LastPostingDate by subtracting 1 from the year. This is the one written the way I think is supposed to work. Sales measures that use this date break the chart.
LastPostingDateLY2 "cheats". It gets year and month from LastPostingDate (as it should), but "cheats" on day, using either a hard-coded day number (in earlier tests) or in this version the day from tempDay2. tempDay2 extracts the day portion of the hard-coded text date "04/17/2016". Sales measures that use this date work correctly.
Revenue YTD LY Current - this is the "correct" measure that is supposed to work, using the correct dates. It breaks the chart.
Revenue YTD LY Current 2 - uses the "cheating" LastPostingDateLY2 for the date instead of the correct date. It works in the chart.
Revenue YTD LY Current 3 - uses newDate instead of the correct LastPostingDateLY. newDate was constructed by parsing LastPostingDateLY into year, month, and day, then putting them back together again with the DATE() function. This measure breaks the chart.
I also experimented with using LastPostingDate instead of LastPostingDateLY (and then subtracting 1 from the year) when calculating newDate, to see if the problem was specifically with LastPostingDateLY, but that version is not in this file. It also broke the chart.
TempYear, newDate, tempYear2, newDate2, etc. were created to test creating dates from the LatestPostingDate measures or from a hard-coded text date. Both seem to work in cards. See the notes above for their effect on the Sales measures and the chart.
I want to point out, in case it's lost in all these different variations, what seems to me to be the key issue:
Measures using a date that was created by a hard-coded DAY value work. Measures using a date that was created by programmatic manipulation of a real date cause an error, an error specifically referring to an invalid DATE value, when used in a chart (but no syntax errors in creating the measure).
- Anonymous9 years agoNot applicable
Hi jblackshear,
>>Measures using a date that was created by programmatic manipulation of a real date cause an error, an error specifically referring to an invalid DATE value, when used in a chart (but no syntax errors in creating the measure).
"Date" function not available on mutiple date, power bi will show you the date function error "date too small or too large". So date function only works on single date calculate, if you want to filter on mutiple date, you can try to use SAMEPERIODLASTYEAR, DATEADD functions. (dateadd also works on your formula)
Regards,
Xiaoxin Sheng