Dynamic two month calculation like Dec 2021 + jan 2022

[vijay_27]

Hello All,
I need in Power BI Desktop:
I need simple calculation with respect to All 12 months like: jan2021+feb2021 value, feb2021+mar2021 value,-----------Nov2021+Dec2021 Then Dec2021 + Jan2022 value I need then Jan2022+Feb2022 value, Feb2022+Mar2022 value and so on....
Every month data will increse dynamically in database table.

Create an additional measure to compare end of month between December 2021 and January 2022 (ensure this new measure is automated and does not require manual updating when new year starts)

Required Scenario: I need two month calculation like today is January 2022 so I need December2021+January2022 value.
Once February 2022 will automatically comes in database table then January2022+February2022 value I need calculation.
Showing in below Image.

Excel link:

https://docs.google.com/spreadsheets/d/1lk4elP54qkuhFMUbIKjxFOvzy1l6T5ZF/edit?usp=sharing&ouid=10046...

[Anonymous · Accepted solution]

Hi @vijay_27 ,

1. Let's assume the MonthCombine is not include in data source, you could use the following formula to get it:

MonthCombine = [Month]&" + " &CALCULATE(MAX('Table'[Month]),FILTER('Table',[Rank]=EARLIER('Table'[Rank])+1)) 

2. Then please create rank column in order to get the value of next month :

YeearMonth = [Year]*100+MONTH( CONVERT([Year]&" "&[Month]&" 1", DATETIME) )

Rank = RANKX('Table',[YeearMonth],,ASC,Dense)

3. Sort MonthCombine column by Rank, the final table is as shown below:

Finally, create a measure:

Measure = SUM('Table'[Value])+ CALCULATE(SUM('Table'[Value]),FILTER(ALLSELECTED('Table'),[Rank]=MAX('Table'[Rank])+1))

Output:

Best Regards,
Eyelyn Qin
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[vijay_27]

 @Anonymous Thank you so much for help this.

[vijay_27]

@amitchandak please help on this