Summarize table - replacing blanks with 0 if there was no entry before

[Anonymous]

Hi all 

I need help with summarizing a table - how do I write a code that would replace blanks with zeros but only in instances when the item has not had any entries prior to that? 

Here is what visual of my data.

Rows - item ids, columns represent MonthNo. 

EG if we look at items 00 and 01, we have sales in March and then blank in May 

On the other hand, Item 05 was introduced in July so I want to insert zeros between August and November but not before July. 

The current query is 

SUMMARIZE(Table A,[ItemId],[Date].[MonthNo],"Sales",SUM(Transactions[QtyABS])

This data is used to calculate rolling monthly average use by using AVERAGEX function. My problem is that the average function  excludes 0s which results in higher average than actual. 

Hope it makes sense 

[parry2k]

@Anonymous solution attached, I think this will do it.

Check my latest blog post Year-2020, Pandemic, Power BI and Beyond to get a summary of my favourite Power BI feature releases in 2020

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

[v-jayw-msft]

Hi @Anonymous ,

Check this topic and see if it's helpful.

https://community.powerbi.com/t5/Desktop/Show-0-in-matrix-when-there-is-no-data-blank-Count-0-does-not/m-p/694140 

Best Regards,

Jay

[AllisonKennedy]

@Anonymous  You could define variables that look for the MIN month for each item, then IF Month > MIN month, SUM() + 0, SUM()

However, this might not be accurate. How do you know when an item is introduced? Do you have a DimItem table with release date or introduced date somewhere that we could access for this calculation?

[Anonymous]

Hi Allison

I assume that the month when the item has appeared in the table for this first time as a month of its introduction. EG - item 05 should have blanks during months 3 to 6 , and then should have 0 between 8 and 10. 

[Ashish_Mathur]

Hi,

Could you share the link from where i can download your PBI file.