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