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...
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.

     

     

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