CODEWITHSUNDEEP
excelpowerbipowerbi-desktop
stefbmt
stefbmt

Reputation: 111

Create Daily Counts based on Date Ranges

Assume that I have the following dataset

Case ID |  Dates Start Date [1]  |   Dates End Date [1]  |
002   | 2020-03-10T00:00:00.000Z  | 2020-03-13T00:00:00.000Z | 
008   | 2020-03-12T00:00:00.000Z  | 2020-03-20T00:00:00.000Z | 
322   | 2020-04-20T00:00:00.000Z  | 2020-04-25T00:00:00.000Z |

IMP: Dates are formatted as follows: 2020-04-11T00:00:00.000Z

I need to create daily count of individuals that were in that location in that particular date range. I can opt to solve this either in PowerBI Desktop or Excel. For example, with this calculation(using the above dataset) I'm expecting to have daily counts like this:

10/03/20 - 1
11/03/20 - 1
12/03/20 - 2
13/03/20 - 2
14/03/20 - 1

I've tried the following solution but somehow it didn't work in my case: Count columns based on date range

Would appreciate your insight! Thanks in advance.

Upvotes: 0

Views: 60

Answers (1)

Ron Rosenfeld
Ron Rosenfeld

Reputation: 60334

You can do this easily with Power Query. I'm not familiar with PowerBI but it might be similar.

  • Set Type of column 1 to Text, and of the Date columns to Whole Number
  • Add a custom column creating a LIST bounded by the Start and End Dates
  • Delete the Original Start and End date columns
  • Expand the List to new rows
  • Change the data type to Date
  • Group by Dates with Count Rows as the Aggregation.

M-Code

let
    Source = Excel.CurrentWorkbook(){[Name="Table3"]}[Content],
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Case ID", type text}, {"Start Date", Int64.Type}, {"End Date", Int64.Type}}),
    #"Added Custom" = Table.AddColumn(#"Changed Type", "Dates", each {[Start Date]..[End Date]}),
    #"Removed Columns" = Table.RemoveColumns(#"Added Custom",{"Start Date", "End Date"}),
    #"Expanded Dates" = Table.ExpandListColumn(#"Removed Columns", "Dates"),
    #"Changed Type1" = Table.TransformColumnTypes(#"Expanded Dates",{{"Dates", type date}}),
    #"Grouped Rows" = Table.Group(#"Changed Type1", {"Dates"}, {{"Count", each Table.RowCount(_), type number}})
in
    #"Grouped Rows"

enter image description here

Upvotes: 1

Related Questions