Looking for DAX statement to distribute value between date range

0
votes

Distribute a value evenly across a start date and end date into daily buckets.

enter image description here

Working with a Date Table TBTR value split evenly between Todays Date - End Date.

There are nine days (3/29-4/7), each day would have a value of 2.91 bucketed by day so that 2.91 per day for that period could ultimately be graphed.

1
powerbidaxpowerquery

1 Answers

0
votes

Here you go, it will create extra rows what you can use to your liking:

    let
        Source = Excel.Workbook(File.Contents("C:\...\Test.xlsx"), null, true),
        Sheet1_Sheet = Source{[Item="Sheet1",Kind="Sheet"]}[Data],
        #"Promoted Headers" = Table.PromoteHeaders(Sheet1_Sheet, [PromoteAllScalars=true]),
        #"Added Custom1" = Table.AddColumn(#"Promoted Headers", "TBTRAverage", each [TBTR] / Duration.Days([End date]-[Todays date])),
        #"Added Custom" = Table.AddColumn(#"Added Custom1", "Date", each let EndThisRow = [End date] in List.Generate(()=>[Todays date], each _ <= EndThisRow , each Date.AddDays( _ , 1))),
        #"Expanded Date" = Table.ExpandListColumn(#"Added Custom", "Date"),
        #"Changed Type" = Table.TransformColumnTypes(#"Expanded Date",{{"Date", type date}})
    in
        #"Changed Type"

Result: enter image description here