Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
Hello:
I am having an issue in Power BI, I am trying to calculate Reveue Margin when the date is not in a consecutive order. It keeps giving me an error. For example if i want to caculate GameStop from 1/21 - 1/26, it gives me an error becuase the dates are not consecutive.
Stock | Date | Stock Price | Changes in Price |
1/20/2021 | $ 209.32 | 0.00% | |
1/21/2021 | $ 26.81 | -87.19% | |
1/22/2021 | $ 104.18 | 288.59% | |
GameStop | 1/21/2021 | $ 43.61 | Infinity |
GameStop | 1/26/2021 | $ 0.55 | Infinity |
Please advise.
Thank you!
Keat B
Solved! Go to Solution.
Hi,
You may download my PBI file from here.
Hope this helps.
@keboon I have no idea how you are doing things at your end, I took this new data and created the same measure and it is working as expected, the image below:
Check my latest blog post Improve UX: Show Year in Legend When Using Time Intelligence Measures | PeryTUS IT Solutions I would ❤ Kudos if my solution helped. 👉 If you can spend time posting the question, you can also make efforts to give Kudos to whoever helped to solve your problem. It is a token of appreciation!
⚡Visit us at https://perytus.com, your one-stop-shop for Power BI-related projects/training/consultancy.⚡
Subscribe to the @PowerBIHowTo YT channel for an upcoming video on List and Record functions in Power Query!!
Learn Power BI and Fabric - subscribe to our YT channel - Click here: @PowerBIHowTo
If my solution proved useful, I'd be delighted to receive Kudos. When you put effort into asking a question, it's equally thoughtful to acknowledge and give Kudos to the individual who helped you solve the problem. It's a small gesture that shows appreciation and encouragement! ❤
Did I answer your question? Mark my post as a solution. Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.
Would you be able to send over your PBI dashboard so i can have a look?
Hi,
You may download my PBI file from here.
Hope this helps.
Hi Ashish:
I am having trouble putting your measures from your dashboard to my dashboard. I have enclosed my dashboard. I have put all your measures into it and the Change in Revenue% is still not calculating [19437994987] Keyword ID which has data on 1/20 & 1/23 only. Your meausres are caclulating for all data that has consecutive dates and not ones that do not have consecutive dates.
That takes me to a sign-in page. Share the download link of your PBI file.
Hi Ashish:
I got it to work! Thank you so much! I was looking at the PBI file with filter for no cost > 0 which threw off the table. 😃
@keboon here is your measure
Price Change % =
VAR __table = ALLEXCEPT ( Game, Game[Stock] )
VAR __currDate = MAX ( Game[Date] )
VAR __currPrice = MAX ( Game[Stock Price] )
VAR __prevDate = CALCULATE ( MAX ( Game[Date] ), __table, Game[Date] < __currDate )
VAR __prevPrice = CALCULATE ( MAX ( Game[Stock Price] ), __table, Game[Date] = __prevDate )
VAR __priceDiff = __currPrice - __prevPrice
VAR __result = DIVIDE ( __priceDiff, __prevPrice, 0 )
RETURN __result
Check my latest blog post Improve UX: Show Year in Legend When Using Time Intelligence Measures | PeryTUS IT Solutions I would ❤ Kudos if my solution helped. 👉 If you can spend time posting the question, you can also make efforts to give Kudos to whoever helped to solve your problem. It is a token of appreciation!
⚡Visit us at https://perytus.com, your one-stop-shop for Power BI-related projects/training/consultancy.⚡
Subscribe to the @PowerBIHowTo YT channel for an upcoming video on List and Record functions in Power Query!!
Learn Power BI and Fabric - subscribe to our YT channel - Click here: @PowerBIHowTo
If my solution proved useful, I'd be delighted to receive Kudos. When you put effort into asking a question, it's equally thoughtful to acknowledge and give Kudos to the individual who helped you solve the problem. It's a small gesture that shows appreciation and encouragement! ❤
Did I answer your question? Mark my post as a solution. Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.
I am still getting the same issue.
Stock ID [18859157114], [19077279539] have non consecutive dates and its Price Change is 0.00%
Stock ID [19313022810] has consecutive dates and is working
I only have one table in the PBI Dashboard with no Date Table.
Stock ID Unit of time Stock Price Price Change %
[18540761682] 1/25/2021 $9.32 0.00%
[18545532711] 1/26/2021 $13.43 0.00%
[18545596481] 1/24/2021 $29.51 0.00%
[18545596533] 1/24/2021 $612.87 0.00%
[18590158015] 1/22/2021 $8.22 0.00%
[18825795354] 1/22/2021 $12.30 0.00%
[18859157114] 1/21/2021 $43.61 0.00%
[18859157114] 1/26/2021 $0.55 0.00%
[18859157190] 1/23/2021 $22.25 0.00%
[19022662327] 1/24/2021 $46.79 0.00%
[19022662352] 1/23/2021 $20.27 0.00%
[19077279539] 1/21/2021 $3.88 0.00%
[19077279539] 1/23/2021 $13.62 0.00%
[19143777805] 1/21/2021 $23.12 0.00%
[19215327139] 1/25/2021 $8.29 0.00%
[19215327170] 1/20/2021 $21.95 0.00%
[19313022236] 1/20/2021 $34.32 0.00%
[19313022810] 1/25/2021 $52.40 0.00%
[19313022810] 1/26/2021 $7.85 -85.02%
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
112 | |
100 | |
80 | |
64 | |
57 |
User | Count |
---|---|
145 | |
111 | |
92 | |
84 | |
66 |