Forecast Recurring revenues

Hi Julia,

 

No apology needed!

 

My general recommendation here is to transform the table so that each row "expands" to include the relevant dates from Start to End. You would add a new column Date which contains the dates within each range. 

 

This will make your life easier when it comes to writing measures and should help with performance, as you can simply sum (or otherwise aggregate) the Value column without any special logic in DAX.

 

I have prepared a small example based on your screenshot (attached PBIX).

 

Assuming your table in the screenshot is contained in a query called DataSource, and you define a parameter Global End Date, you can create a query like this to do the transformation:

let
  Source = DataSource,
  #"Add Date Lists" = Table.AddColumn(
    Source,
    "Date",
    each
      let
        Start = [Start],
        End   = [End] ?? #"Global End Date"
      in
        if [Type] = "OTR" then
          List.Generate(() => Start, each _ <= End, each Date.AddDays(_, 1))
        else if [Type] = "Monthly" then
          List.Generate(() => Start, each _ <= End, each Date.AddMonths(_, 1))
        else if [Type] = "Quarterly" then
          List.Generate(() => Start, each _ <= End, each Date.AddQuarters(_, 1))
        else
          {}
  ),
  #"Expanded Date" = Table.ExpandListColumn(#"Add Date Lists", "Date"),
  #"Changed Type" = Table.TransformColumnTypes(#"Expanded Date", {{"Date", type date}})
in
  #"Changed Type"

 

This query simply repeats Value for every date in the range (ending on Global End Date if End is null). If this logic is not right (e.g. you need to allocate the value in some way) it would need some adjustment. But this should illustrate the basic structure you can use to do this transformation. You can also remove redundant columns after this transformation (I didn't do so).

 

You can then add a Date table, related to the new Date column, to allow you to group/filter by date in the report.

 

Sample visual would look like this:

 

Hopefully that is some help, but please post back if needed.

 

Regards