Power BI is turning 10! Tune in for a special live episode on July 24 with behind-the-scenes stories, product evolution highlights, and a sneak peek at what’s in store for the future.
Save the dateEnhance your career with this limited time 50% discount on Fabric and Power BI exams. Ends August 31st. Request your voucher.
Hello!
I would like to create multiple year-over-year indexes to compare a numerical value for a given period with the same period a year before. This also works well for column and line charts but I can't manage to use them in tables or matrices. My data kinda looks like the following:
date | device | country | costs | sales |
11/1/2021 | tablet | US | 50 | 250 |
11/1/2021 | desktop | NO | 45 | 300 |
11/1/2021 | mobile | BE | 30 | 350 |
11/1/2021 | mobile | CA | 5 | 200 |
The following DAX formula to create a measure works really well on a card or for example line or column charts:
Country | Costs | Costs YoY Index |
AE | 5500 | 108 |
AO | 6540 | 113 |
AR | 7839 | 115 |
AT | 2300 | 85 |
I get the error: "Can't display the visual. See details" - MdxScript(model)(4,124) Calculation error in measure '_Calculations[Costs YoY Index]: Function 'DATEADD' expects a contiguous selections when date column is not unique, has gaps or contains time portion.
Does somebody know how to solve this?
Great thanks!
Hi @Anonymous ,
Thanks again for your reply.
I can now indeed add it to my table. Unfortunately however the numbers don't make sense anymore now. Also changing the hierachy to month doesnt help as I get the same original error.
Hi @Dirkdr ,
You can add date to the visual.
The measure is correct! Just ensure that the date field in the visual .
How to Get Your Question Answered Quickly
If it does not help, please provide more details with your desired output and pbix file without privacy information (or some sample data) .
Best Regards
Community Support Team _ Rongtie
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Hi @Anonymous,
Thank you for your reply! Adding the date field indeed fixes the error.
However, my table gets very big when I add the date field as for every country and every date combination it is listed in the table. Ideally, I only want to show the following:
Country | Costs | Costs YoY Index |
AE | 5500 | 108 |
AO | 6540 | 113 |
AR | 7839 | 115 |
AT | 2300 | 85 |
While when I add the date I get something like this:
Country | Date | Costs | Costs YoY Index |
AE | 11/1/2021 | 250 | 102 |
AE | 11/2/2021 | 320 | 111 |
AE | 11/3/2021 | 400 | 85 |
AE | 11/4/2021 | 100 | 120 |
Thanks a lot!
Hi @Dirkdr ,
You can modify the measure.
Costs YoY Index = var _1=year(maxx(all(data),date[date]))
return
DIVIDE(SUM(data[costs]), CALCULATE(SUM(data[costs]), year[date[date]=_1-1)) * 100
Or you can change the date hierarchy to the month.
If it does not help, please provide more details with your desired output and pbix file without privacy information (or some sample data) .
Best Regards
Community Support Team _ Rongtie
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
User | Count |
---|---|
25 | |
11 | |
7 | |
6 | |
6 |
User | Count |
---|---|
30 | |
13 | |
11 | |
9 | |
6 |