Calculate Revenue Margin when Date is not in consecutive order

[keboon]

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
Google	1/20/2021	$   209.32	0.00%
Google	1/21/2021	$     26.81	-87.19%
Google	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

[parry2k]

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

[keboon]

Would you be able to send over your PBI dashboard so i can have a look?

[Ashish_Mathur · Accepted solution]

Hi,

You may download my PBI file from here.

Hope this helps.

[keboon]

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

[Ashish_Mathur]

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

[keboon]

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]

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

[keboon]

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%