Show continuous date and reflect '0' for missing data

[Anonymous]

Hi All

I think I might have accidentally double post and marked as spam, I have deleted the previous thread

I have the below table which contain delivery information - the date of delivery, for which customer (cust no.) and the product and amount of volume (vol).

However it only reflects days when there's a delivery:

Delivered Date	Cust No.	Doc No.	Vol
9/1/2020 12:00:00 AM	CHS156	ML9589	2690
9/1/2020 12:00:00 AM	CHS156	MM0667	9821
9/2/2020 12:00:00 AM	CHS256	ML9946	3464
9/2/2020 12:00:00 AM	CHS156	MM0316	2346
9/2/2020 12:00:00 AM	CHS156	MM0320	5677
9/2/2020 12:00:00 AM	CHS156	MM0317	5245
9/3/2020 12:00:00 AM	CHS256	MM0477	3522
9/5/2020 12:00:00 AM	CHS156	MM0741	3256

However I like a table to reflect all the days, even with no delivery (reflecting "0" for vol), for each respective customer. 

I would think it should look something like this:

Delivered Date	Cust No.	Doc No.	Vol
9/1/2020 12:00:00 AM	CHS156	ML9589	2690
9/1/2020 12:00:00 AM	CHS156	MM0667	9821
9/1/2020 12:00:00 AM	CHS256		0
9/2/2020 12:00:00 AM	CHS256	ML9946	3464
9/2/2020 12:00:00 AM	CHS156	MM0316	2346
9/2/2020 12:00:00 AM	CHS156	MM0320	5677
9/2/2020 12:00:00 AM	CHS156	MM0317	5245
9/3/2020 12:00:00 AM	CHS256	MM0477	3522
9/3/2020 12:00:00 AM	CHS156		0
9/4/2020 12:00:00 AM	CHS256		0
9/4/2020 12:00:00 AM	CHS156		0
9/5/2020 12:00:00 AM	CHS156	MM0741	3256
9/5/2020 12:00:00 AM	CHS256		0

Where if I were to filter by customer no. , i will still see continous dates for each customer no.

Previously I tried doing the below but it's didnt work out:

Where I created a continous calendar table and merge with this delivery table

New Table = 
GENERATEALL (
    'Calendar',
    VAR Tdate ='Calendar'[Date]
    RETURN
        SELECTCOLUMNS (
            CALCULATETABLE ( 'DO (Merge)', 'DO (Merge)'[Delivery Date Only] = Tdate ),
            "DO Delivered Date",'DO (Merge)'[Delivery Date Only],
            "Customer No", 'DO (Merge)'[Customer No]
        )

but it seems to only fill up those entirely missing dates and not for respective customer no.

Thanks

[v-alq-msft · Accepted solution]

Hi, @Anonymous 

Based on your description, I created data to reproduce your scenario, The pbix file is attached in the end.

Table:

You may create a new query and paste the following codes in 'Advanced Editor'.

let
    Source = let mindate=List.Min(Table[Delivered Date]),maxdate=List.Max(Table[Delivered Date]) in
List.Dates(
    mindate,
    Duration.Days( maxdate-mindate)+1,
    #duration(1,0,0,0)
),
    #"Converted to Table" = Table.FromList(Source, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
    #"Renamed Columns" = Table.RenameColumns(#"Converted to Table",{{"Column1", "Delivered Date"}}),
    #"Added Custom" = Table.AddColumn(#"Renamed Columns", "Cust", each Table.Distinct(
    Table.FromList( Table[Cust No],Splitter.SplitByNothing(),null,null,ExtraValues.Error )
)),
    #"Expanded Cust" = Table.ExpandTableColumn(#"Added Custom", "Cust", {"Column1"}, {"Cust.Column1"}),
    #"Added Custom1" = Table.AddColumn(#"Expanded Cust", "Custom", each let 
date = [Delivered Date],cust=[Cust.Column1],
tab = Table.SelectRows(
    Table,
    each [Delivered Date]=date and 
    [Cust No]=cust
)[Doc No]
in 
tab
),
    #"Expanded Custom" = Table.ExpandListColumn(#"Added Custom1", "Custom"),
    #"Added Custom2" = Table.AddColumn(#"Expanded Custom", "Custom.1", each let 
date = [Delivered Date],cust=[Cust.Column1],
c=[Custom],
val = List.Sum( Table.SelectRows(
    Table,
    each [Delivered Date]=date and 
    [Cust No]=cust and 
    [Doc No]=c
)[Vol]
)
in 
if val=null 
then 0
else val 
),
    #"Renamed Columns1" = Table.RenameColumns(#"Added Custom2",{{"Cust.Column1", "Cust No."}, {"Custom", "Doc No."}, {"Custom.1", "Vol"}})
in
    #"Renamed Columns1"

Result:

Best Regards

Allan

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

[FarhanAhmed]

I think you should Create Customer & Date tables.

Create a measure of Volume that will return "0" if it is blank.

Then use Date & Customer from those tables and enable "Show items with No Data" in table or Matrix visual to get the desired results.

[Anonymous]

Hi 

Thanks for the response.

I'm not sure if i got it correctly.

Do you mean i should separate out the Customer No. as a different table? and that will leave me with 3 tables (date, customer and delivery?)

How would you recommend I manage a relationship between these 3 tables.