cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Helper I
Helper I

Show continuous date and reflect '0' for missing data

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 DateCust No.Doc No. Vol
9/1/2020 12:00:00 AMCHS156ML95892690
9/1/2020 12:00:00 AMCHS156MM06679821
9/2/2020 12:00:00 AMCHS256ML99463464
9/2/2020 12:00:00 AMCHS156MM03162346
9/2/2020 12:00:00 AMCHS156MM03205677
9/2/2020 12:00:00 AMCHS156MM03175245
9/3/2020 12:00:00 AMCHS256MM04773522
9/5/2020 12:00:00 AMCHS156MM07413256

 

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 DateCust No.Doc No. Vol
9/1/2020 12:00:00 AMCHS156ML95892690
9/1/2020 12:00:00 AMCHS156MM06679821
9/1/2020 12:00:00 AMCHS256 0
9/2/2020 12:00:00 AMCHS256ML99463464
9/2/2020 12:00:00 AMCHS156MM03162346
9/2/2020 12:00:00 AMCHS156MM03205677
9/2/2020 12:00:00 AMCHS156MM03175245
9/3/2020 12:00:00 AMCHS256MM04773522
9/3/2020 12:00:00 AMCHS156 0
9/4/2020 12:00:00 AMCHS256 0
9/4/2020 12:00:00 AMCHS156 0
9/5/2020 12:00:00 AMCHS156MM07413256
9/5/2020 12:00:00 AMCHS256 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

1 ACCEPTED SOLUTION

Accepted Solutions
Community Support
Community Support

Hi, @ngzongyi 

 

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

Table:

b1.png

 

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"

 

b2.png

 

Result:

b3.png

 

Best Regards

Allan

 

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

View solution in original post

3 REPLIES 3
Memorable Member
Memorable Member

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.

 

 

 

Did I answer your question? Mark my post as a solution! Appreciate your Kudos!!

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.

Community Support
Community Support

Hi, @ngzongyi 

 

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

Table:

b1.png

 

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"

 

b2.png

 

Result:

b3.png

 

Best Regards

Allan

 

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

View solution in original post

Helpful resources

Announcements
Community Conference

Power Platform Community Conference

Check out the on demand sessions that are available now!

Community Conference

Microsoft Power Platform Communities

Check out the Winners!

secondImage

Create an end-to-end data and analytics solution

Learn how Power BI works with the latest Azure data and analytics innovations at the digital event with Microsoft CEO Satya Nadella.

Top Solution Authors
Top Kudoed Authors