Repeat the end value of Rolling change to future months

[Sandhya877]

Hi I am trying to figure out the below logic.

I have a salary field and Slicer with selection of months ranging 1 to 12. 

Example: Salary for Current month is 5000. 

If I select the Months as 5. 

I need to divide salary by number of months selected and used that value for the next 5 months. 

5000/5 = 1000. 

I am trying to figure out a way to get the below Rolling change using DAX.

Date                 Base Value      Rolling Change

06/01/2023.       1000                   1000

07/01/2023        1000                  2000

08/01/2023.        1000                 3000

09/01/2023.        1000                 4000

10/01/2023.       1000                  5000

11/01/2023                                  5000

12/01/2023                                 5000

[Anonymous · Accepted solution]

Hi @Sandhya877,

You can use the calendar table as axis of the table, then write measure formula to calculate the rolling value with +0 to expand and the repeat the previous calculation result on the following date ranges which not has match values.

formula =
VAR currDate =
    MAX ( Calendar[Date] )
RETURN
    CALCULATE (
        SUM ( Table[Value] ),
        FILTER ( ALLSELECTED ( Table ), [Date] <= currDate )
    ) + 0

Regards,

Xiaoxin Sheng