Line chart show 0 for missing data and when slicer applied

[Binway]

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:

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

SUM(MonthlySales[Sales]) + IF (__date >= __min && __date <= __max, 0)

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

[amitchandak]

@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]

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]

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]

Hi Dedmon,

I was thinking a SUMX with a filter but the missing records are not appearing and the filtere does not work. I even tried ADDMISSINGITEM but can't seem to filter the dates before the Min and Max

EVALUATE
VAR __min = CALCULATE ( MIN ( MonthlySales[month_starting]), ALLSELECTED())
VAR __max = CALCULATE ( MAX ( MonthlySales[month_starting]), ALLSELECTED())
VAR sales =
    SUMMARIZE (
        MonthlySales,
        MonthlySales[Business],
        date_dim[first_day_of_month],
        "QTY", SUM ( MonthlySales[Sales] ) + 0
    )
RETURN
    SUMX(FILTER(date_dim,
        date_dim[first_day_of_month]>= __min 
        &&
        date_dim[first_day_of_month]<= __max
        ),
    sales,[QTY])

[v-deddai1-msft]

Hi @Binway ,

Please try to use the following measure:

measure =
VAR __min =
    CALCULATE ( MIN ( MonthlySales[month_starting] ), ALLSELECTED () )
VAR __max =
    CALCULATE ( MAX ( MonthlySales[month_starting] ), ALLSELECTED () )
VAR sales =
    SUMMARIZE (
        MonthlySales,
        MonthlySales[Business],
        date_dim[first_day_of_month],
        "QTY", SUM ( MonthlySales[Sales] ) + 0
    )
RETURN
    SUMX (
        FILTER (
            sales,
            date_dim[first_day_of_month] >= __min
                && date_dim[first_day_of_month] <= __max
        ),
        [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]

Sorry Dedmon - same result where the +0 is just not working if you filter it.  How would I create a "virtual table" with all the missing values, then filter that.

Thanks - appreciate the efforts.

[v-deddai1-msft]

Hi @Binway ,

It's better that you can share us some sample pbix file.

Best Regards,

Dedmon Dai

[Binway]

Thanks for looking at this.

You should be able to get the pbix from google drive here - LineChartTest 

All I am trying to do is add 0 for the missing values when connected to the date_dim, which works fine but no matter what I do I can't then filter this data down to just display the date values in the data table.

Binway

[v-deddai1-msft]

Hi @Binway ,

Please check the permission of the shared file. I can't download it.

Best Regards,

Dedmon Dai

[Binway]

Not sure why it did that anyway here is a new link.

https://drive.google.com/file/d/1fqXrM1UZxt-sAOsOHTElOxK12_sPQuyx/view?usp=sharing 

[v-deddai1-msft · Accepted solution]

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]

Thanks so much Demon,

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

Thanks again.

Binway

[Binway]

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.

[Binway]

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))