Forum Discussion
Helper IILine 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/2015 | FloatyMcBoat | 42 |
| 01/03/2015 | FloatyMcBoat | 16 |
| 01/04/2015 | FloatyMcBoat | 31 |
| 01/05/2015 | RimRecker | 1 |
| 01/05/2015 | FloatyMcBoat | 33 |
| 01/06/2015 | FloatyMcBoat | 35 |
| 01/07/2015 | FloatyMcBoat | 158 |
| 01/08/2015 | FloatyMcBoat | 55 |
| 01/09/2015 | RimRecker | 3 |
| 01/09/2015 | FloatyMcBoat | 26 |
| 01/10/2015 | RimRecker | 78 |
| 01/11/2015 | RimRecker | 12 |
| 01/11/2015 | FloatyMcBoat | 23 |
| 01/12/2015 | RimRecker | 3 |
| 01/01/2016 | RimRecker | 3 |
| 01/01/2016 | FloatyMcBoat | 17 |
| 01/02/2016 | RimRecker | 64 |
| 01/02/2016 | FloatyMcBoat | 21 |
| 01/03/2016 | RimRecker | 60 |
| 01/03/2016 | FloatyMcBoat | 11 |
| 01/04/2016 | RimRecker | 3 |
| 01/04/2016 | FloatyMcBoat | 10 |
| 01/05/2016 | RimRecker | 99 |
| 01/05/2016 | FloatyMcBoat | 7 |
| 01/06/2016 | RimRecker | 19 |
| 01/06/2016 | FloatyMcBoat | 33 |
| 01/07/2016 | RimRecker | 90 |
| 01/07/2016 | FloatyMcBoat | 60 |
| 01/08/2016 | RimRecker | 7 |
| 01/08/2016 | FloatyMcBoat | 27 |
| 01/09/2016 | FloatyMcBoat | 189 |
| 01/10/2016 | RimRecker | 8 |
| 01/10/2016 | FloatyMcBoat | 49 |
| 01/11/2016 | RimRecker | 2 |
| 01/11/2016 | FloatyMcBoat | 25 |
| 01/12/2016 | RimRecker | 2 |
| 01/12/2016 | FloatyMcBoat | 34 |
Whislt the data is at the Month Grain the Date Dim is at the Daily level but I don't think this is causing an issue - a sample of the date dim is below and not complete.
| date_actual | first_day_of_month |
| 01/01/2015 00:00 | 01/01/2015 00:00 |
| 02/01/2015 00:00 | 01/01/2015 00:00 |
| 03/01/2015 00:00 | 01/01/2015 00:00 |
| 04/01/2015 00:00 | 01/01/2015 00:00 |
| 05/01/2015 00:00 | 01/01/2015 00:00 |
| 06/01/2015 00:00 | 01/01/2015 00:00 |
| 07/01/2015 00:00 | 01/01/2015 00:00 |
| 08/01/2015 00:00 | 01/01/2015 00:00 |
| 09/01/2015 00:00 | 01/01/2015 00:00 |
| 10/01/2015 00:00 | 01/01/2015 00:00 |
| 11/01/2015 00:00 | 01/01/2015 00:00 |
| 12/01/2015 00:00 | 01/01/2015 00:00 |
| 13/01/2015 00:00 | 01/01/2015 00:00 |
| 14/01/2015 00:00 | 01/01/2015 00:00 |
| 15/01/2015 00:00 | 01/01/2015 00:00 |
| 16/01/2015 00:00 | 01/01/2015 00:00 |
| 17/01/2015 00:00 | 01/01/2015 00:00 |
| 18/01/2015 00:00 | 01/01/2015 00:00 |
| 19/01/2015 00:00 | 01/01/2015 00:00 |
| 20/01/2015 00:00 | 01/01/2015 00:00 |
| 21/01/2015 00:00 | 01/01/2015 00:00 |
| 22/01/2015 00:00 | 01/01/2015 00:00 |
| 23/01/2015 00:00 | 01/01/2015 00:00 |
| 24/01/2015 00:00 | 01/01/2015 00:00 |
| 25/01/2015 00:00 | 01/01/2015 00:00 |
| 26/01/2015 00:00 | 01/01/2015 00:00 |
| 27/01/2015 00:00 | 01/01/2015 00:00 |
| 28/01/2015 00:00 | 01/01/2015 00:00 |
| 29/01/2015 00:00 | 01/01/2015 00:00 |
| 30/01/2015 00:00 | 01/01/2015 00:00 |
| 31/01/2015 00:00 | 01/01/2015 00:00 |
| 01/02/2015 00:00 | 01/02/2015 00:00 |
| 02/02/2015 00:00 | 01/02/2015 00:00 |
I have attempted different DAX code to create the measure with the closest I have come using:
Which doesn't quite do the job:
I am wondering if it is possible to display the data - I have also attempted the show items with no value and the +0 at the end of the code.
Thanks in Advance
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 RESULTIf this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Best Regards,
Dedmon Dai
14 Replies
- amitchandak
Super User
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
BinwayThnaks 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])RETURNCALCULATE(CALCULATE(SUM(MonthlySales[Sales]))+0,FILTER( date_dim, date_dim[first_day_of_month] >= __min && date_dim[first_day_of_month] <= __max))- Binway
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-msftCommunity Support
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
