Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Get certified in Microsoft Fabric—for free! For a limited time, the Microsoft Fabric Community team will be offering free DP-600 exam vouchers. Prepare now

Reply
vijay_27
Helper I
Helper I

Dynamic two month calculation like Dec 2021 + jan 2022

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.


Result.jpg

Excel link:

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

1 ACCEPTED SOLUTION
v-eqin-msft
Community Support
Community Support

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:

 

Eyelyn9_1-1642729084563.png

 

Finally, create a measure:

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

Output:

Eyelyn9_2-1642729170008.png

 

 

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.

View solution in original post

3 REPLIES 3
v-eqin-msft
Community Support
Community Support

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:

 

Eyelyn9_1-1642729084563.png

 

Finally, create a measure:

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

Output:

Eyelyn9_2-1642729170008.png

 

 

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.

 @v-eqin-msft Thank you so much for help this.

vijay_27
Helper I
Helper I

@amitchandak please help on this

 

Helpful resources

Announcements
OCT PBI Update Carousel

Power BI Monthly Update - October 2024

Check out the October 2024 Power BI update to learn about new features.

September Hackathon Carousel

Microsoft Fabric & AI Learning Hackathon

Learn from experts, get hands-on experience, and win awesome prizes.

October NL Carousel

Fabric Community Update - October 2024

Find out what's new and trending in the Fabric Community.