Forum Discussion

vijay_27's avatar
vijay_27
Icon for Helper I rankHelper I
4 years ago
Solved

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.




Excel link:

https://docs.google.com/spreadsheets/d/1lk4elP54qkuhFMUbIKjxFOvzy1l6T5ZF/edit?usp=sharing&ouid=100468019232737567054&rtpof=true&sd=true

Need Help
  • Anonymous's avatar
    Anonymous
    4 years ago

    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.

3 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    4 years ago

    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's avatar
      vijay_27
      Icon for Helper I rankHelper I
      4 years ago

       Anonymous Thank you so much for help this.