Forum Discussion

CrazyPirate's avatar
CrazyPirate
Frequent Visitor
1 year ago
Solved

30 Year Plan based upon field values

I have been tasked with creating a visual for a 30 year plan, which plots the costs over the 30 years by year.

 

The table below is an example of the types of field involved.

I need to see a cost for everytime each row is due over the 30 years. Note there is only 1 row per Property and Asset so the future costs need to be calculated based upon the Next Date and Duration.

 

E.g. So for example the kitchen will have a cost every 15 years (180 months)  so will appear twice.

01/11/2039,

01/11/2054

 

I am not sure where to start on this one,

So many thanks in advance,

Cheers

Rich

Need Help
  • rajendraongole1's avatar
    rajendraongole1
    1 year ago

    Hi CrazyPirate -You'll need to create a new table that generates due dates according to the Next Date and Duration for each property and asset.

    FutureCosts1 =
        GENERATE(
            'PropT',
            VAR BaseDate = FIRSTNONBLANK('PropT'[Next Date], 'PropT'[Next Date])
            VAR Duration = FIRSTNONBLANK('PropT'[Duration (in Months)], 'PropT'[Duration (in Months)])
            VAR NumberOfPayments = INT(30 / (Duration / 12))
            RETURN
                ADDCOLUMNS(
                    GENERATESERIES(1, NumberOfPayments, 1),
                    "Due Date", EDATE(BaseDate, Duration * [Value]),
                    "Cost1", FIRSTNONBLANK('Propt'[Cost], 'PropT'[Cost])
                )
        )

    create a new table as like below:

     

    Use a line chart or a bar chart to plot costs against due dates.Customize the visuals with legends and tooltips for clarity.

    Hope it works at your end.

     

     

2 Replies

  • rajendraongole1's avatar
    rajendraongole1
    Super User
    1 year ago

    Hi CrazyPirate -You'll need to create a new table that generates due dates according to the Next Date and Duration for each property and asset.

    FutureCosts1 =
        GENERATE(
            'PropT',
            VAR BaseDate = FIRSTNONBLANK('PropT'[Next Date], 'PropT'[Next Date])
            VAR Duration = FIRSTNONBLANK('PropT'[Duration (in Months)], 'PropT'[Duration (in Months)])
            VAR NumberOfPayments = INT(30 / (Duration / 12))
            RETURN
                ADDCOLUMNS(
                    GENERATESERIES(1, NumberOfPayments, 1),
                    "Due Date", EDATE(BaseDate, Duration * [Value]),
                    "Cost1", FIRSTNONBLANK('Propt'[Cost], 'PropT'[Cost])
                )
        )

    create a new table as like below:

     

    Use a line chart or a bar chart to plot costs against due dates.Customize the visuals with legends and tooltips for clarity.

    Hope it works at your end.

     

     

    • CrazyPirate's avatar
      CrazyPirate
      Frequent Visitor
      1 year ago

      Hi rajendraongole1 ,

       Thanks so much for the reply and I can confirm that worked a treat. I have not used the Generate or GenerateSeries before so great opportunity to understand it thank you,

      Cheers

      Rich