Forum Discussion
Create YoY Measure without error A date column containing duplicate dates was specified in the call
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?
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.
5 Replies
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.
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
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.
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.
