Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
7 years ago
Solved

Sum items between dates for each date

The title might be confusing, but I think it shouldn't be difficult.

I have the following data sample:

There we have clients, start date, end date, and items.

With query editor or DAX, I want to generate a calendar table that sums the items between the start and end date (DateStart<=Date<=DateEnd) for each client. It should look like this:

And with that table, I will create the following line graph, that will sum all items per date and with a slicer for each client:

 

 

Here is a sample of my .pbix: https://www.dropbox.com/s/rfy0fax3ik6bh4l/Sum_between_dates.pbix?dl=0

 

Thanks in advance!

 

Estefania

Need Help
  • Anonymous's avatar
    Anonymous
    7 years ago

    Nevermind, I was able to transform the table in the Query Editor

    Here's the code

    let
    Source = Excel.Workbook(File.Contents("C:\Users\e.ordaz.maurera\OneDrive - Accenture\Analytics + AI\IC\Mock.xlsx"), null, true),
    Table1_Table = Source{[Item="Table1",Kind="Table"]}[Data],
    #"Changed Type" = Table.TransformColumnTypes(Table1_Table,{{"Client", type text}, {"DateStart", type date}, {"DateEnd", type date}, {"Items", Int64.Type}}),
    #"Inserted Date Subtraction" = Table.AddColumn(#"Changed Type", "Subtraction", each Duration.Days([DateEnd]-[DateStart])),
    #"Added Custom" = Table.AddColumn(#"Inserted Date Subtraction", "Date", each List.Dates([DateStart],[Subtraction],#duration(1,0,0,0))),
    #"Expanded List" = Table.ExpandListColumn(#"Added Custom", "Date"),
    #"Removed Columns" = Table.RemoveColumns(#"Expanded List",{"DateStart", "DateEnd", "Subtraction"}),
    #"Grouped Rows" = Table.Group(#"Removed Columns", {"Client", "Date"}, {{"ItemsSum", each List.Sum([Items]), type number}}),
    #"Changed Type1" = Table.TransformColumnTypes(#"Grouped Rows",{{"Date", type date}})
in
    #"Changed Type1"

    Thanks to this post on http://radacad.com/dates-between-merge-join-in-power-query

     

1 Reply

  • Anonymous's avatar
    Anonymous
    Not applicable
    7 years ago

    Nevermind, I was able to transform the table in the Query Editor

    Here's the code

    let
    Source = Excel.Workbook(File.Contents("C:\Users\e.ordaz.maurera\OneDrive - Accenture\Analytics + AI\IC\Mock.xlsx"), null, true),
    Table1_Table = Source{[Item="Table1",Kind="Table"]}[Data],
    #"Changed Type" = Table.TransformColumnTypes(Table1_Table,{{"Client", type text}, {"DateStart", type date}, {"DateEnd", type date}, {"Items", Int64.Type}}),
    #"Inserted Date Subtraction" = Table.AddColumn(#"Changed Type", "Subtraction", each Duration.Days([DateEnd]-[DateStart])),
    #"Added Custom" = Table.AddColumn(#"Inserted Date Subtraction", "Date", each List.Dates([DateStart],[Subtraction],#duration(1,0,0,0))),
    #"Expanded List" = Table.ExpandListColumn(#"Added Custom", "Date"),
    #"Removed Columns" = Table.RemoveColumns(#"Expanded List",{"DateStart", "DateEnd", "Subtraction"}),
    #"Grouped Rows" = Table.Group(#"Removed Columns", {"Client", "Date"}, {{"ItemsSum", each List.Sum([Items]), type number}}),
    #"Changed Type1" = Table.TransformColumnTypes(#"Grouped Rows",{{"Date", type date}})
in
    #"Changed Type1"

    Thanks to this post on http://radacad.com/dates-between-merge-join-in-power-query