Forum Discussion

annaka05's avatar
annaka05
Helper I
3 years ago

Fill blank fields with 0

Hello, 

I have matrix, which is showing last 12 months sales, month by month. I have blank fields there and I want to fill them with 0. Matrix has column with date in format yyyy.mm and sales measure as below:

I have tried with adding +0 and inserting if loop, but nothing helped.

It works fine, when I have add month column from date hierarchy, but when I am using date column with yyyy.mm format blanks are not filling with 0. I cannot use month column from date hierarchy, because sometimes, when date range, selected in slicer is not in the same year, then I need to sort columns by yyyy.mm (from oldest to newest).

 

Regards,

Anna

Need Help

2 Replies

  • amitchandak's avatar
    amitchandak
    Super User
    3 years ago

    annaka05 , try somthing like this with help from date table

     

    0 between range
    Measure = var _1= SUM(Opportunity[Opportunity count]) +0
    var _min = minx(ALLSELECTED('Calendar'), 'Calendar'[Date])
    var _max = maxx(ALLSELECTED('Calendar'), 'Calendar'[Date])
    return
    CALCULATE(if(max('Calendar'[Date]) <_min || max('Calendar'[Date]) >_max , BLANK(), _1))

     

    • annaka05's avatar
      annaka05
      Helper I
      3 years ago

      Hello @amitchandak ,

      Thank you for your reply. Unfortunatelly, this solution did not fill blanks in my matrix.

       

      Anna