Week over Week Qty Change = VAR lastWeekAmount = CALCULATE ( SUM ( Merge1[Qty To Return

[Anonymous]

Hello Communit  -   My source data has zeros  (not blanks) but I am still getting the output in my matrix table (below).    

How can I clean this up to where the blanks show as 0% instead?    Also, it seems like -100% shows up randomly.    

I am trying to evaluate week over week change based on how many defective units were returned each week.  

Week over Week Qty Change =

VAR lastWeekAmount =

CALCULATE (

SUM ( Merge1[Qty To Return] ),

FILTER (

ALL ( 'Date Table' ),

'Date Table'[Week Number]

= MIN ( 'Date Table'[Week Number] ) - 1

)

)

RETURN

DIVIDE ( SUM ( Merge1[Qty To Return] ) - lastWeekAmount, lastWeekAmount, 0 )

[v-diye-msft]

Hi @Anonymous 

You might consider providing your dummy pbix that would be folpful for us to investigate it further. 

You can upload it to the onedrive for business and share the link here. please don't forget to disclose the expected results and remove the confidential info.

[Anonymous]

Hello, 

I realized my error...actually the two formulas are computing different things.   One is week over week change, the other is each week's prior sales.    When I converted that column to a whole number, it made sense.  

However....there is one very strange thing happening which I cannot make sense of.     All of the week to week percents make sense, until you get to the 1764.15% row.      For example, a change from 68 to 79 is indeed 16.18%.     17 to 87 is a 411% change, etc.  

But a change from 79 to 1976 should be 2401%.   Not 1764%.    I really can't figure out why that one number would be calculating incorrectly and different from all of the others?  

[mahoneypat]

This kind of behavior usually suggests you have duplicate row error in your SUMX (or other iterator).  Or one of them is iterating over week granularity and the other is not (similar to total row errors).

If this works for you, please mark it as the solution.  Kudos are appreciated too.  Please let me know if not.

Regards,

Pat

[amitchandak]

@Anonymous ,You can try +0. But that will do left join

refer if my blogs can help.

https://medium.com/@amitchandak.1978/power-bi-wtd-questions-time-intelligence-4-5-98c30fab69d3
https://community.powerbi.com/t5/Community-Blog/Week-Is-Not-So-Weak-WTD-Last-WTD-and-This-Week-vs-La...

calendar

https://community.powerbi.com/t5/Community-Blog/Any-Weekday-Week-Decoding-Date-and-Calendar-2-5-Powe...