topic Re: Create YoY Measure without error A date column containing duplicate dates was specified in the call in DAX Commands and Tips

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

rks408 — Thu, 31 Oct 2024 20:33:56 GMT

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?

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

Anonymous — Fri, 01 Nov 2024 02:35:01 GMT

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.

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

rks408 — Fri, 01 Nov 2024 13:31:31 GMT

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

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

Sahir_Maharaj — Sun, 03 Nov 2024 14:56:58 GMT

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.

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

rks408 — Mon, 04 Nov 2024 14:48:14 GMT

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.

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

Anonymous — Tue, 05 Nov 2024 09:13:33 GMT

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.