Forum Discussion

madhurmalhotra's avatar
madhurmalhotra
Frequent Visitor
6 years ago
Solved

ROLL FORWARD TO NEXT YEAR

Need Help
  • v-kelly-msft's avatar
    v-kelly-msft
    6 years ago

    Hi madhurmalhotra ,

     

    First create 2 calculated columns as below:

     

    Year = YEAR('Table'[Date])
    _diff = 
var _minyear=MINX(FILTER(ALL('Table'),'Table'[Entity ]=EARLIER('Table'[Entity ])),'Table'[Year])
Return
'Table'[Year]-_minyear

     

    Then create a measure as below:

     

    Measure = 
var _a= SUMX(FILTER(ALL('Table'),'Table'[Entity ]=MAX('Table'[Entity ])&&'Table'[Year]<=MAX('Table'[Year])),'Table'[Profit])
var _minyear=MINX(FILTER(ALL('Table'),'Table'[Entity ]=MAX('Table'[Entity ])),'Table'[Year])
Return
IF(MAX('Table'[_diff])<=1,_a,IF(MAX('Table'[_diff])>=2,SWITCH(MAX('Table'[_diff]),2,_a+CALCULATE(MAX('Table'[Profit]),FILTER(ALL('Table'),'Table'[Entity ]=MAX('Table'[Entity ])&&'Table'[Year]=_minyear)),3,_a+CALCULATE(MAX('Table'[Profit]),FILTER(ALL('Table'),'Table'[Entity ]=MAX('Table'[Entity ])&&'Table'[Year]=_minyear))*3+CALCULATE(MAX('Table'[Profit]),FILTER(ALL('Table'),'Table'[Entity ]=MAX('Table'[Entity ])&&'Table'[Year]=_minyear+1))
)))

     

    Finally you will see:

    For the related .pbix file,pls click here.

     

    Best Regards,
    Kelly
    Did I answer your question? Mark my post as a solution!

3 Replies

  • amitchandak's avatar
    amitchandak
    Super User
    6 years ago

    madhurmalhotra , this will give you trailing year data

    Year behind Sales = CALCULATE(SUM(Sales[Sales Amount]),dateadd('Date'[Date],-1,Year))

     

    Looking ay you need, you might union your results

    • madhurmalhotra's avatar
      madhurmalhotra
      Frequent Visitor
      6 years ago

      Hi amitchandak ,

       

      There is a second screenshot ,below the first one.

      Can you please suggest a solution for the second ss.

       

      Thanks a lot for the first one!

      • v-kelly-msft's avatar
        v-kelly-msft
        Community Support
        6 years ago

        Hi madhurmalhotra ,

         

        First create 2 calculated columns as below:

         

        Year = YEAR('Table'[Date])
        _diff = 
var _minyear=MINX(FILTER(ALL('Table'),'Table'[Entity ]=EARLIER('Table'[Entity ])),'Table'[Year])
Return
'Table'[Year]-_minyear

         

        Then create a measure as below:

         

        Measure = 
var _a= SUMX(FILTER(ALL('Table'),'Table'[Entity ]=MAX('Table'[Entity ])&&'Table'[Year]<=MAX('Table'[Year])),'Table'[Profit])
var _minyear=MINX(FILTER(ALL('Table'),'Table'[Entity ]=MAX('Table'[Entity ])),'Table'[Year])
Return
IF(MAX('Table'[_diff])<=1,_a,IF(MAX('Table'[_diff])>=2,SWITCH(MAX('Table'[_diff]),2,_a+CALCULATE(MAX('Table'[Profit]),FILTER(ALL('Table'),'Table'[Entity ]=MAX('Table'[Entity ])&&'Table'[Year]=_minyear)),3,_a+CALCULATE(MAX('Table'[Profit]),FILTER(ALL('Table'),'Table'[Entity ]=MAX('Table'[Entity ])&&'Table'[Year]=_minyear))*3+CALCULATE(MAX('Table'[Profit]),FILTER(ALL('Table'),'Table'[Entity ]=MAX('Table'[Entity ])&&'Table'[Year]=_minyear+1))
)))

         

        Finally you will see:

        For the related .pbix file,pls click here.

         

        Best Regards,
        Kelly
        Did I answer your question? Mark my post as a solution!