Expand monthly total with date range to show total by day

[Anonymous]

Maybe it can be done without expanding the table, but here's what I have:

The table I'm working with has records with a monthly price and date range for billable dates in the month:

What I need to do is create a matrix that will show the daily price by day and customer, like below:

What I think needs to happen is to make the table expand each record for each date in the date range? Similar to below.

Any ideas on how to get to the end goal? I need to show every day for the selected Period.

[v-shex-msft]

Hi @Anonymous,

You can also do these operations in the power query editor.

Please take a look at below m query code and replace 'Raw query table' and column names in 'SelectRows' and 'Added Custom' (highlight parts) with raw your table and fields names:

let
    Source = 'Raw query table'
    #"SelectRows" = Table.SelectColumns(#"Changed Type",{"ID", "Start", "End"}),
    #"Added Custom" = Table.AddColumn(SelectRows, "Expand", each List.Dates([Start],Duration.Days([End]-[Start]),#duration(1,0,0,0))),
    #"Expanded Expand" = Table.ExpandListColumn(#"Added Custom", "Expand")
in
    #"Expanded Expand"

Spoiler

let
Source = 'Raw query table'
#"SelectRows" = Table.SelectColumns(#"Changed Type",{"ID", "Start", "End"}),
#"Added Custom" = Table.AddColumn(SelectRows, "Expand", each List.Dates([Start],Duration.Days([End]-[Start]),#duration(1,0,0,0))),
#"Expanded Expand" = Table.ExpandListColumn(#"Added Custom", "Expand")
in
#"Expanded Expand"

Regards,
Xiaoxin Sheng

[dedelman_clng · Accepted solution]

HI @Anonymous -

First of all, assumptions (these are necessary to programmatically compute the Daily Price)

1) The start date and end date will always be within the same month

2) "Period" will always be the last day of the month

Hopefully this is the end result you are looking for...

1) Create a date table (I use CALENDARAUTO()). Do not link it to any model tables.

2) Calculate the table that will have all of the dates in it, whether or not it is in between a start and end date

AllDates = 
ADDCOLUMNS (
    CROSSJOIN (
        DateTab,
        SUMMARIZE (
            'Price',
            'Price'[ID],
            'Price'[Customer],
            'Price'[Period],
            'Price'[Start Date],
            'Price'[End Date],
            'Price'[Monthly Price]
            )
    ),
    "Daily Price", IF ( [Date] >= [Start Date] && [Date] <= [End Date], 
                         [Monthly Price] / DAY([Period]), BLANK () )
)

3) Create Matrix with

-  AllDates[ID], AllDates[Customer], AllDates[Period] as Rows (turn off Stepped Layout)

-  AllDates[Date] as Columns (be sure to enable "Show items with no data")

-  SUM(AllDates[Daily Price] as Values

You'll need to play around with some of the formatting settings to get it just how you want it.  Also, if the assumptions at the top are not correct, you will need to find a way to get "Daily Price" as a column in the original data set.

Hope this helps

David