Problem with date construction using DATE function

[jblackshear]

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.

[v-shex-msft]

Hi @jblackshear,

Can you share some sample file to test? I test on some sample data without any issue.

Regards,

Xiaoxin Sheng

[jblackshear]

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).

[v-shex-msft · Accepted solution]

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

[jblackshear]

(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.

[Vvelarde]

@jblackshear

Hi, try using this:

Revenue YTD LY Current 4 = CALCULATE(SalesDataAggregated[Revenue YTD], DATESBETWEEN(Dates[Date], DATE(2016,1,1),[LatestPostingDateLY]))