DAX TOTALYTD() - Duplicate Date Error

[Dave]

I’m having issues with the DAX TOTALYTD() function. Here is my formula:

                YTD = TOTALYTD(SUM(SampleSheet[Value]), SampleSheet[Date])

However, I’m getting the error below when I try to display a simple table with the YEAR and YTD columns:

Error Text: MdxScript(Model) (1, 41) Calculation error in measure 'SampleSheet'[YTD Value]: A date column containing duplicate dates was specified in the call to function 'DATESYTD'. This is not supported.

What is the best approach to resolve this issue of duplicate dates? The source data does have duplicate dates, but I want to retain the actual dates from my source data. I've done some searching for this issue online, but haven't been able to find a satisfactory answer yet. 

Here is a link to my .pbx file. Regarding the data source, the dates were taken from a Dynamics CRM system and I added some dummy data in the “Value” Column.

[konstantinos · Accepted solution]

Actually there are duplicate values in your table..( same date in multiple rows )..plus you are using time intelligent functions TOTALYTD which in DAX need a seperate date table/dimention. 

So you need to create a date table ( one row for every date ) and relate that to SampleSheet[Date].

Then your formula will be   YTD = TOTALYTD(SUM(SampleSheet[Value]), Datetable[Date])

There are 3 at least ways to create - add a date table such as load it from an external source, create one in PQ with M language, create one in data model with DAX functions CALENDAR() or CALENDARAUTO()...A simple search in community or Google it will guide you for all solutions.