Create YoY Measure without error A date column containing duplicate dates was specified in the call

[rks408]

I am a novice at PowerBI and I'm very confused with measures and dates at the moment so any guidance would help. I'm trying to create a few year over year charts/tables that are counting the number of items created by the date created. This is what I have right now:

• A date field Created On
• A title field Title
• A measure ItemCount = COUNT(table[Title]) 
• A measure ItemCount LY = CALCULATE(COUNT(table[Title]), DATEADD(table[Created On], -1, YEAR)) 

I then tried to check if my measures worked by creating a table. I put the Created On field with only the year in the date hierarchy and the measure ItemCount. That came out fine. But when I tried to add ItemCount LY I received the error "A date column containing duplicate dates was specified in the call to function 'DATEADD'"

I researched this error and I understand that measures need aggregated data to work and I guess the created on field that I was referencing has multiple items with the same date. But I don't understand how to proceed. How can I get unique dates? I've looked up functions that could retrieve only unique dates, or I've seen people suggest creating a date table, but this report is not using a directquery connection. So my ability to make new columns and tables is disabled which seems to be hindering me a lot. I can only create measures. Is there any way forward? 

[Anonymous · Accepted solution]

Hi @rks408 

Thank you very much Sahir_Maharaj for your prompt reply.

Based on the screenshot information you provided, I made some adjustments to this solution:

Create measures.

ItemCount = COUNT('Count Table'[Title])

ItemCount LY = 
CALCULATE(
    COUNT('Count Table'[Title]),
    FILTER(
        'Count Table',
        YEAR('Count Table'[Created On]) <= YEAR(TODAY()) - 1
    )
)

Here is the result.

Regards,

Nono Chen

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Anonymous]

Hi @rks408 

Here's some dummy data

Try this:

ItemCount LY = 
CALCULATE(
    COUNT('Count Table'[Title]),
    YEAR('Count Table'[Created On]) = YEAR(MAX('Count Table'[Created On])) - 1
)

Here is the result.

If you're still having problems, provide some dummy data and the desired outcome. It is best presented in the form of a table.

Regards,

Nono Chen

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[rks408]

Hello, thank you for your reply. This got rid of my error message but the data isn't populating in the table correctly. 

For reference:

The year property of the date hierarchy for Created On is being used. The dummy data you provided reflects what my data looks like. When I had Created On as just the date and not the hierarchy, 208 did populate for 2018's ReportCountLY but when the data is aggregated it is not working. Thank you 

[Sahir_Maharaj]

Hello @rks408, and thank you @Anonymous for this excellent approach.

Building on the DAX by Nono Chen, can you please try this approach:

ItemCount LY = 
CALCULATE(
    COUNT('Count Table'[Title]),
    FILTER(
        ALL('Count Table'[Created On]),
        YEAR('Count Table'[Created On]) = YEAR(MAX('Count Table'[Created On])) - 1
    )
)

Hope this helps.

[rks408]

Hi Sahir, 

I am still having issues with the aggregated data. As you can see when I use just the date for Created On, it is calculating 208 for all of 2018 at the row level and when I use the year from the Created On date hierarchy it is blank. Is there perhaps a different way I should be formatting Created On? Once again I am limited as I cannot create a new table or columns.