Forum Discussion

Binway's avatar
Binway
Icon for Helper II rankHelper II
5 years ago
Solved

Line chart show 0 for missing data and when slicer applied

Hi Folks, I have a data and date table where I am trying to have the line chart drop to 0 when there is missing data. month_starting Business Sales 01/01/2015 FloatyMcBoat 24 01/02/201...
Need Help
  • v-deddai1-msft's avatar
    v-deddai1-msft
    5 years ago

    Hi Binway ,

     

    Please change the relationship between your date table and fact table to single direction

     

     

    Then use the following measure:

     

    New Amounts = 
VAR __min = CALCULATE ( MIN ( MonthlySales[month_starting]), ALLSELECTED(MonthlySales))
VAR __max = CALCULATE ( MAX ( MonthlySales[month_starting]), ALLSELECTED(MonthlySales))
VAR RESULT = IF(MAX(date_dim[first_day_of_month])>=DATE(YEAR(__min-10),MONTH(__min-10),1)&&MAX(date_dim[first_day_of_month])<=__max ,
                        SUM(MonthlySales[Sales])+0) return RESULT

     

     

    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

     

    Best Regards,

    Dedmon Dai

amitchandak's avatar
amitchandak
Icon for Super User rankSuper User
5 years ago

Binway , Try like this

 

All Amounts =
VAR __min = CALCULATE ( MIN ( MonthlySales[month_starting]), ALLSELECTED())
VAR __max = CALCULATE ( MAX ( MonthlySales[month_starting]), ALLSELECTED())
RETURN
calculate(SUM(MonthlySales[Sales]) , filter( date, date_dim[first_day_of_month] >= __min && date_dim[first_day_of_month] <= __max)) + 0

 

 

  • Binway's avatar
    Binway
    Icon for Helper II rankHelper II
    5 years ago

    Thnaks Amit,

    That code is very close.  Unfortuntely the +0 returns all the values in the dim_date which I would have thought the filter code would have taken care of.

    I changed the final line to add another calculate to try create all the values including 0 and then filter but that is not working either:

    All Amounts =
    VAR __min = CALCULATE ( MIN ( MonthlySales[month_starting]), ALL())
    VAR __max = CALCULATE ( MAX ( MonthlySales[month_starting]), ALL())
    VAR __date = MAX(date_dim[first_day_of_month])
    RETURN
    CALCULATE(CALCULATE(SUM(MonthlySales[Sales]))+0,FILTER( date_dim, date_dim[first_day_of_month] >= __min && date_dim[first_day_of_month] <= __max))

     

  • Binway's avatar
    Binway
    Icon for Helper II rankHelper II
    5 years ago

    After doing a bit more investigation it looks like a SUMMARIZECOLUMNS may be a viable option

    EVALUATE 
    SUMMARIZECOLUMNS(date_dim[first_day_of_month],
                        MonthlySales[Business],
                        "QTY", SUM(MonthlySales[Sales])+0
                        )

    From what I can tell this produces a "virtual table" of the data that I could perhaps filter where the date_dim dates are within the data range.

    But I can't seem to get the filter to work such as wrapping a calculate around it.

     

    Thnaks

    Binway

    • v-deddai1-msft's avatar
      v-deddai1-msft
      Icon for Community Support rankCommunity Support
      5 years ago

      Hi Binway ,

       

      Please try to use the following measure:

       

      All Amounts =
VAR sales =
    SUMMARIZE (
        MonthlySales,
        MonthlySales[Business],
        date_dim[first_day_of_month],
        "QTY", SUM ( MonthlySales[Sales] ) + 0
    )
RETURN
    SUMX ( sales, [QTY] )

       

       

      If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

       

      Best Regards,

      Dedmon Dai

      • Binway's avatar
        Binway
        Icon for Helper II rankHelper II
        5 years ago

        Sorry Dedmon but the 0 value is not appearing in the chart.

         

        I will try a filter for the date in the last line similiar to other formulas and let you know how I go.