DATEADD error message for visuals

[cdawidow]

I created 2 measures, Current Year MTD Sales and LYMTD Sales.  

LYMTD Sales = CALCULATE([Total Sales],DATESMTD(DATEADD('ORDHDT01 (2)'[Fiscal Dates],-12,MONTH)))

MTD Sales = TOTALMTD([Total Sales],'ORDHDT01 (2)'[Fiscal Dates])

But I am now trying to put these measures in a basic visual but as I use the months from my fiscal dates field to showcase the changes over the months, I get this error message. Any ideas why?

[Anonymous]

All time-intel functions necessarily need a proper Date table. A proper date table has a column with dates in it which are continuous, unique and cover full years. Just create such a table and connect to your fact table. Everything will then work perfectly well. If you really want to create good and correct models, please structure your data as a star schema. Power BI has been designed to work with star schemas in the best possible way. You can even read this in the official documentation.

[AntrikshSharma]

Is this table ORDHDT01 (2) a calendar table with all the dates for whole calendar/fiscal year? Also if you are not slicing data by dates then also you will get the same error. Remove every field from the visual apart from the columns coming from the dates table.

[amitchandak]

@cdawidow , DATEADD need continuous dates. that is why always suggested to use date table with time intelligence.

example

MTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESMTD('Date'[Date]))
last MTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESMTD(dateadd('Date'[Date],-1,MONTH)))
last month Sales = CALCULATE(SUM(Sales[Sales Amount]),previousmonth('Date'[Date]))
last MTD (complete) Sales =  CALCULATE(SUM(Sales[Sales Amount]),DATESMTD(ENDOFMONTH(dateadd('Date'[Date],-1,MONTH))))

To get the best of the time intelligence function. Make sure you have a date calendar and it has been marked as the date in model view. Also, join it with the date column of your fact/s. Refer :
https://radacad.com/creating-calendar-table-in-power-bi-using-dax-functions
https://www.archerpoint.com/blog/Posts/creating-date-table-power-bi
https://www.sqlbi.com/articles/creating-a-simple-date-table-in-dax/

See if my webinar on Time Intelligence can help: https://community.powerbi.com/t5/Webinars-and-Video-Gallery/PowerBI-Time-Intelligence-Calendar-WTD-Y...


Appreciate your Kudos.

[Greg_Deckler]

@cdawidow - It's hard to say really without sample data. The time "intelligence" functions are so black box it's really tough to troubleshoot them when they don't work. You may find this helpful - https://community.powerbi.com/t5/Community-Blog/To-bleep-With-Time-Intelligence/ba-p/1260000

Also, see if my Time Intelligence the Hard Way provides a different way of accomplishing what you are going for.

https://community.powerbi.com/t5/Quick-Measures-Gallery/Time-Intelligence-quot-The-Hard-Way-quot-TIT...

Not really enough information to go on, please first check if your issue is a common issue listed here: https://community.powerbi.com/t5/Community-Blog/Before-You-Post-Read-This/ba-p/1116882

Also, please see this post regarding How to Get Your Question Answered Quickly: https://community.powerbi.com/t5/Community-Blog/How-to-Get-Your-Question-Answered-Quickly/ba-p/38490

The most important parts are:
1. Sample data as text, use the table tool in the editing bar
2. Expected output from sample data
3. Explanation in words of how to get from 1. to 2.