Issue with MonthYear using it as a date in a measure to do prior year calculate

[Anonymous]

Measure is:

Units PY1 = CALCULATE (

      [Units],

      DATEADD( DATESYTD ('Tablename'[MonthYear].[Date]), -1,YEAR) )

My MonthYear in Tablename has a format of Data Type = Date and format = mmmm yyyy

I have a date table with that format as well, but not sure if that matters.

The error message I get is:

"Column reference to 'MonthYear' in table 'Tablename' cannot be used with a variation 'Date' because it does not have any."

Also, my company does not allow me to put sample data online for reference.  So "Tablename" is not the real name, just for reference in this question.

[Anonymous]

I did not resolve this issue; I went another direction.

I have my data hierarchies off, and I do not use them.  I had a calendar table.  I was planning to try building a date yyyy-mm-01, but I because of another issue that there is no solution for yet in Power BI, I had to move to building the report in Excel.  I am not having the same issue with my dates with my model data when I tied it to Excel.  I appreciate your effort.

[Anonymous]

I did not resolve this issue; I went another direction.

I have my data hierarchies off, and I do not use them.  I had a calendar table.  I was planning to try building a date yyyy-mm-01, but I because of another issue that there is no solution for yet in Power BI, I had to move to building the report in Excel.  I am not having the same issue with my dates with my model data when I tied it to Excel.  I appreciate your effort.

[Anonymous]

I have a calendar (aka date table) as I mentioned in my first message, but I am still getting the error.

[lbendlin]

Use the date column from the calendar table for your DATEADD calculations, not the [MonthYear].[Date] column from 'Tablename' 

[Anonymous]

I changed the table to the calendar table within my equation.  My error is still the same.  Is it maybe and issue with the format of month/year being  mmmm yyyy?  I have used this formula before in another model but the month year format was month number then year.

[lbendlin]

The format is most likely not the issue. Time intelligence functions usually fail if the underlying date column is not contiguous and not covering.

[Anonymous]

I am going to try changing my date format to mm/yyyy (number month) to see if that works.

[Anonymous]

I reviewed a youtube video from Two Guys in a cube.  Double checked my date format to make sure the caalendar and tablename dates aligned, and they do now.  One thing I am wondering, I had to create a coloumn for MonthYear in Table name, so it is a calculated column.  I had month (name) and year in my source data and both are text format.  Power BI would not let me change the format type to date on those columns.  I am afraid this is what is causing my issues.

[lbendlin]

Add a fictional day to your calculated column - "yyyy-mm-01" . Then you can convert that to a date. 

[lbendlin]

Try to avoid using the built in date hierarchies. Instead, add a proper calendar table to your data model.  That will make the time intelligence functions work properly too.