Forecast Recurring revenues

[JuliaXSynaos]

Hi everyone,

I'm very new to Power BI and currently learning, so sorry if my question is confusing or easy to answer.

I have a data table which has line with different revenue types and dates.

Here's a snippet:

What I want as outcome is a list where the revenues are in the related month columns. But, what I need additionaly is to forecast them to the future. Means if the type is "Quarterly" I want it to appear every fourth month until the End date is reached (monthy every month and so on...).

Any ideas how I can set this up?

Thank you in advance!

Julia

[OwenAuger · Accepted solution]

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