Forum Discussion

Binway's avatar
Binway
Helper 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

v-deddai1-msft's avatar
v-deddai1-msft
Community Support
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

Binway's avatar
Binway
Helper II
5 years ago

Thanks so much Demon,

I'll see how it works on the real data.

 

Thanks again.

Binway