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.
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
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
@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.⚡
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.
Hi @Anonymous ,
Check this topic and see if it's helpful.
Best Regards,
Jay
@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?
Copying DAX from this post? Click here for a hack to quickly replace it with your own table names
Has this post solved your problem? Please Accept as Solution so that others can find it quickly and to let the community know your problem has been solved.
If you found this post helpful, please give Kudos C
I work as a Microsoft trainer and consultant, specialising in Power BI and Power Query.
www.excelwithallison.com
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.
Hi,
Could you share the link from where i can download your PBI file.
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 |
---|---|
115 | |
100 | |
88 | |
68 | |
61 |
User | Count |
---|---|
152 | |
120 | |
102 | |
87 | |
68 |