split value between dates

[LoryMenCR]

Dear All,

I have a table with three columns:

Cost

Start Date

End Date

I need to get to a table/matrix view where I show the Cost divided for (End Date - Start Date) with a DAY granularity.

My Table:

My desired result:

Any idea?
Thanks a lot

[NaveenGandhi · Accepted solution]

Hi @LoryMenCR 

I have created a power query solution for this. Check the attached file or use below m code.

let
    Source = Table,
    Custom1 = Table.AddColumn(
        Source, 
        "Granularity", 
        each 
            let
                DayCount = Duration.Days(Duration.From([End Date] - [Start Date]))+1,
                DayOffsetList = {0..DayCount},
                CostList = List.Repeat({[Cost] / DayCount}, DayCount),
                DateList = List.Dates([Start Date], DayCount, #duration(1,0,0,0)),
                YearList = List.Transform(DateList, each  Date.MonthName(_)&"-"&Text.From(Date.Year(_)))
            in
                Table.FromColumns(
                    {DateList,YearList, CostList}, 
                    {"Date", "MonthYear", "Cost"}
                )
    ),
    ExpandedGranularity = Table.ExpandTableColumn(Custom1, "Granularity", {"Date", "MonthYear", "Cost"}, {"Granularity.Date", "Granularity.MonthYear", "Granularity.Cost"}),
    GroupedRows = Table.Group(ExpandedGranularity, {"Granularity.MonthYear"}, {{"Cost", each List.Sum([Granularity.Cost]), type number}})
in
    GroupedRows

Above numbers are the accurate numbers if split on days. 

If this post helps, then please consider Accept it as the solution to help the others find it more quickly. Appreciate you kudos!!

Follow me on LinkedIn!!!

[Anonymous · Accepted solution]

Hi @LoryMenCR ,

If you don't want to transform your data in Power Query Editor, you can try Dax to create a measure.

We need to add a DimDate table to help calculation.

DimDate = ADDCOLUMNS(CALENDARAUTO(),"Year",YEAR([Date]),"Month",MONTH([Date]),"YearMonth",FORMAT([Date],"MMM-YYYY"),"YearMonthSort",YEAR([Date])*100+MONTH([Date]))

Measure:

Measure = 
VAR _Virtual =
    GENERATE ( 'Table', CALENDAR ( 'Table'[Start Date] + 1, 'Table'[End Date] ) )
VAR _ADDCOLUMNS =
    ADDCOLUMNS ( _Virtual, "Datediff1", DATEDIFF ( [Start Date], [End Date], DAY ) )
RETURN
    SUMX (
        FILTER (
            _ADDCOLUMNS,
            [Date] >= MIN ( DimDate[Date] )
                && [Date] <= MAX ( DimDate[Date] )
        ),
        DIVIDE ( [Cost], [Datediff1] )
    )

Result is as below.

If you want to use Power Query,  NaveenGandhi's workaround is a good way, you just need to do some update on it.

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMjQwMFDSUTIyMDLRNzAEIjjHSN/AQilWJ1rJ2ARFiaEpkhIjiBJTJBVGaIYYK8XGAgA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Cost = _t, #"Start Date" = _t, #"End Date" = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Cost", Int64.Type}, {"Start Date", type date}, {"End Date", type date}}),
     Custom1 = Table.AddColumn(#"Changed Type", "Granularity", each let
                DayCount = Duration.Days(Duration.From([End Date] - [Start Date])),
                DayOffsetList = {0..DayCount},
                CostList = List.Repeat({[Cost] / DayCount}, DayCount),
                DateList = List.Dates(Date.AddDays([Start Date],1), DayCount, #duration(1,0,0,0)),
                YearList = List.Transform(DateList, each  Date.MonthName(_)&"-"&Text.From(Date.Year(_)))
            in
                Table.FromColumns(
                    {DateList,YearList, CostList}, 
                    {"Date", "MonthYear", "Cost"}
                )),
    #"Expanded Granularity" = Table.ExpandTableColumn(Custom1, "Granularity", {"Date", "MonthYear", "Cost"}, {"Date", "MonthYear", "Cost.1"}),
    #"Grouped Rows" = Table.Group(#"Expanded Granularity", {"MonthYear"}, {{"Count", each List.Sum([Cost.1]), type number}})
in
    #"Grouped Rows"

Result is as below.

Best Regards,
Rico Zhou

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

[NaveenGandhi · Accepted solution]

Hi @LoryMenCR 

I have created a power query solution for this. Check the attached file or use below m code.

let
    Source = Table,
    Custom1 = Table.AddColumn(
        Source, 
        "Granularity", 
        each 
            let
                DayCount = Duration.Days(Duration.From([End Date] - [Start Date]))+1,
                DayOffsetList = {0..DayCount},
                CostList = List.Repeat({[Cost] / DayCount}, DayCount),
                DateList = List.Dates([Start Date], DayCount, #duration(1,0,0,0)),
                YearList = List.Transform(DateList, each  Date.MonthName(_)&"-"&Text.From(Date.Year(_)))
            in
                Table.FromColumns(
                    {DateList,YearList, CostList}, 
                    {"Date", "MonthYear", "Cost"}
                )
    ),
    ExpandedGranularity = Table.ExpandTableColumn(Custom1, "Granularity", {"Date", "MonthYear", "Cost"}, {"Granularity.Date", "Granularity.MonthYear", "Granularity.Cost"}),
    GroupedRows = Table.Group(ExpandedGranularity, {"Granularity.MonthYear"}, {{"Cost", each List.Sum([Granularity.Cost]), type number}})
in
    GroupedRows

Above numbers are the accurate numbers if split on days. 

If this post helps, then please consider Accept it as the solution to help the others find it more quickly. Appreciate you kudos!!

Follow me on LinkedIn!!!