Forum Discussion
Problem with date construction using DATE function
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
(I posted this in Desktop discussion but wonder if it's a bug so repeating it here)
I have a need for year-to-date and previous year-to-date figures that always use the current date (actually, the maximum posting date available in the data warehouse, which is usually the previous business day), and not a date slicer or filter.
I have created these two measures in my Dates table
LatestPostingDate = CALCULATE(LASTDATE(SalesDataAggregated[GLPostingDate]), ALL(Dates[Date]))
LatestPostingDateLY = DATE(YEAR(Dates[LatestPostingDate])-1, MONTH(Dates[LatestPostingDate]), DAY(Dates[LatestPostingDate]))
These measures work correctly in cards:
Now I want a year-to-date measure for this date last year.
Revenue YTD LY Current 2 = CALCULATE(SalesDataAggregated[Revenue YTD], DATESBETWEEN(Dates[Date], DATE(2016,1,1), DATE(2016,4,17)))
Note: "Current" in my measure name means it uses the current date as described above, not a date slicer or filter.
This works fine, in a column in a table or matrix, or in a card. Obviously I do not want to use hard-coded dates in my measure, but this was for troubleshooting purposes, because of what I will show next.
Now I try to replace those hard-coded dates with dynamic dates using the measures I created.
Revenue YTD LY Current 3 = CALCULATE(SalesDataAggregated[Revenue YTD], DATESBETWEEN(Dates[Date], DATE(2016,1,1), Dates[LatestPostingDateLY]))
Adding this measure to a table visualization (with Division as the rows) generates this error:
The error message refers to the Dates measure LatestPostingDateLY, rather than the Sales measure that I just added to the matrix. Remember that the Dates measure LatestPostingDateLY works fine in a card, accepted a Date format, and looks just like LatestPostingDate, which works.
Still trying to figure out where the problem is, I modified the LatestPostingDateLY measure to use a hard-coded number for the DAY value.
LatestPostingDateLY = DATE(YEAR(Dates[LatestPostingDate])-1, MONTH(Dates[LatestPostingDate]), 17)
I no longer get the error on LatestPostingDateLY. Instead I get the following error on the YTD calculation.
I have looked at this in every which way I can think of. Am I doing something crazy that I am just not seeing? Or is there a problem with the date calculation?
Thanks.