Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Anonymous
Not applicable

Expand monthly total with date range to show total by day

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:

smileamile_0-1598232129810.png

 

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

Testa.JPG

 

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

smileamile_1-1598232165140.png

 

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

1 ACCEPTED SOLUTION
dedelman_clng
Community Champion
Community Champion

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

 

2020-08-24 15_16_55-Untitled - Power BI Desktop.png

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

 

View solution in original post

2 REPLIES 2
v-shex-msft
Community Support
Community Support

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

Community Support Team _ Xiaoxin
If this post helps, please consider accept as solution to help other members find it more quickly.
dedelman_clng
Community Champion
Community Champion

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

 

2020-08-24 15_16_55-Untitled - Power BI Desktop.png

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

 

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.