cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
keboon
Microsoft
Microsoft

Calculate Revenue Margin when Date is not in consecutive order

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.

 

StockDateStock PriceChanges in Price
Google1/20/2021 $   209.320.00%
Google1/21/2021 $     26.81-87.19%
Google1/22/2021 $   104.18288.59%
GameStop1/21/2021 $     43.61Infinity
GameStop1/26/2021 $       0.55Infinity

Please advise.
Thank you!

Keat B

1 ACCEPTED SOLUTION
Ashish_Mathur
Super User
Super User

Hi,

You may download my PBI file from here.

Hope this helps.

Untitled.png


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/

View solution in original post

8 REPLIES 8
parry2k
Super User
Super User

@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:

 

parry2k_0-1614201825254.png

 

 

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.






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?

Ashish_Mathur
Super User
Super User

Hi,

You may download my PBI file from here.

Hope this helps.

Untitled.png


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/

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.

 

https://msit.powerbi.com/reportEmbed?reportId=b28e1a31-dac4-461d-a098-4f4c13054308&autoAuth=true&cti... 

That takes me to a sign-in page.  Share the download link of your PBI file.


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/

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. 😃

parry2k
Super User
Super User

@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.






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%

Helpful resources

Announcements
August 2022 update 768x460.jpg

Check it Out!

Click here to learn more about the August 2022 updates!

August 1 episode 9_no_dates 768x460.jpg

The Power BI Community Show

Watch the playback when Priya Sathy and Charles Webb discuss Datamarts! Kelly also shares Power BI Community updates.

Power Platform Conf 2022 768x460.jpg

Join us for Microsoft Power Platform Conference

The first Microsoft-sponsored Power Platform Conference is coming in September. 100+ speakers, 150+ sessions, and what's new and next for Power Platform.

Top Solution Authors
Top Kudoed Authors