Convert # of months to Monthly Data

[lotus22]

Is there a way to take a date and if you know number of months...convert the value to monthly?

For example, if you know you are implementing savings for 5 months - starting on Nov 16, 2023 -> say it is $500 but it is $100 in Nov 2023, Dec 2023, Jan 2024, Feb 2024, March 2024

For example below, I need to show 91,588 as monthly over 12 months starting Feb 13, 2023.

[TheoC]

@lotus22  You can use a Matrix visual. 

- Add the Month to the "Column" 

- Add the Title to the "Rows"

- Add the Measure to the "Values".

Did the formula I provided earlier work?

Thanks heaps,
Theo

[TheoC]

Hi @lotus22 

Based on the data, I assume you want a Start Date and a Savings Amount that is based on each specific scenario in your data?

If that is the case, you can try something like the below measure:

Monthly Distribution = 

VAR StartDate = MAX ( 'Table'[StartDate] ) // Replace with your StartDate table and column name

VAR TotalAmount = SUM ( 'Table'[Amount] ) // Replace with your Amount table and column name

VAR Months = 12 // If the number of months for distribution change, you can replace this (i.e. qtrly, half yearly, etc).

VAR EndDate = EDATE ( StartDate , Months )

VAR CurrentMonth = MONTH ( TODAY() ) // Returns the current month

VAR CurrentYear = YEAR ( TODAY() ) // Returns the current year

VAR CurrentDate = DATE ( CurrentYear , CurrentMonth , 1 )

RETURN

    IF (

        AND ( CurrentDate >= StartDate , CurrentDate < EndDate) , 
        TotalAmount / Months ,
        0
    )

Let me know how you go!

Theo 🙂

[lotus22]

Hi @TheoC , thanks for the reply. The month is a dynamic field. Each of the savings has different monthly accruals. Some have 12, but some have less than 12 or more than 12.

[TheoC]

Hi @lotus22 

Excellent.  The measure allows for you to modify it accordingly. The only change is the VAR Months so the measure will be:

Monthly Distribution = 

VAR StartDate = MAX ( 'Table'[StartDate] ) // Replace with your StartDate table and column name

VAR TotalAmount = SUM ( 'Table'[Amount] ) // Replace with your Amount table and column name

VAR Months = MAX ( 'Table'[NumberOfMonthsField] ) // Replace with your Months table and column name

VAR EndDate = EDATE ( StartDate , Months )

VAR CurrentMonth = MONTH ( TODAY() ) // Returns the current month

VAR CurrentYear = YEAR ( TODAY() ) // Returns the current year

VAR CurrentDate = DATE ( CurrentYear , CurrentMonth , 1 )

RETURN

    IF (

        AND ( CurrentDate >= StartDate , CurrentDate < EndDate) , 
        TotalAmount / Months ,
        0
    )

Thanks heaps 🙂

Theo

[lotus22]

Thank you @TheoC 

I need to show data in the format below. How do I do this?