Forum Discussion

NBOnecall's avatar
NBOnecall
Helper V
6 years ago
Solved

Rolling Measure calculation

Hi,   I have two mesaures that I would like to display for rolling 12 months. They are GMROII and Turns. I have a feeling this should be easy, but can't figure it out. I would like to be able to di...
Need Help
  • lc_finance's avatar
    lc_finance
    6 years ago

    Hi NBOnecall ,

     

     

    below is the solution I propose to you:

     

    1) create a new calculated column in the Dimtime table to use in your matrix. 

    Year Month = year([Date])&" "&FORMAT('Dimtime'[Date],"mmmm")

    2) create a new calculated column in the Dimtime table to correctly sort the previous column

    Year Month Order = value(year([Date])&FORMAT(month([Date]),"00"))

    3) Create 2 new measures:

    GMROII rolling last 12 months = 
VAR currentDate = LASTDATE('Dimtime'[Date])
VAR sameDateLastYear = NEXTDAY(SAMEPERIODLASTYEAR(currentDate))

RETURN 
 CALCULATE(
     [GMROII],
     DATESBETWEEN('Dimtime'[Date], sameDateLastYear, currentDate)
 )
    Turns rolling last 12 months = 
VAR currentDate = LASTDATE('Dimtime'[Date])
VAR sameDateLastYear = NEXTDAY(SAMEPERIODLASTYEAR(currentDate))

RETURN 
 CALCULATE(
     [Turns],
     DATESBETWEEN('Dimtime'[Date], sameDateLastYear, currentDate)
 )

    You can find an updated version of your Power BI attached here:

    https://drive.google.com/file/d/1644H9YhLfPuWbE9laXMfTQLf6T2KVvXn/view?usp=sharing

     

    Let me know if it works!

     

    LC

    Interested in Power BI templates? Check my blog at www.finance-bi.com

     

lc_finance's avatar
lc_finance
Solution Sage
6 years ago

Hi NBOnecall ,

 

I would need to understand the problem better to propose a solution, that's why I'd recommend sharing a sample Power BI file.

I understand that as it takes a long time, you'd rather avoid it.

 

Maybe someone else seeing this post can think of a solution without having more info.

 

Best of luck for finding a solution,

 

LC

  • lc_finance's avatar
    lc_finance
    Solution Sage
    6 years ago

    Hi NBOnecall ,

     

     

    below is the solution I propose to you:

     

    1) create a new calculated column in the Dimtime table to use in your matrix. 

    Year Month = year([Date])&" "&FORMAT('Dimtime'[Date],"mmmm")

    2) create a new calculated column in the Dimtime table to correctly sort the previous column

    Year Month Order = value(year([Date])&FORMAT(month([Date]),"00"))

    3) Create 2 new measures:

    GMROII rolling last 12 months = 
VAR currentDate = LASTDATE('Dimtime'[Date])
VAR sameDateLastYear = NEXTDAY(SAMEPERIODLASTYEAR(currentDate))

RETURN 
 CALCULATE(
     [GMROII],
     DATESBETWEEN('Dimtime'[Date], sameDateLastYear, currentDate)
 )
    Turns rolling last 12 months = 
VAR currentDate = LASTDATE('Dimtime'[Date])
VAR sameDateLastYear = NEXTDAY(SAMEPERIODLASTYEAR(currentDate))

RETURN 
 CALCULATE(
     [Turns],
     DATESBETWEEN('Dimtime'[Date], sameDateLastYear, currentDate)
 )

    You can find an updated version of your Power BI attached here:

    https://drive.google.com/file/d/1644H9YhLfPuWbE9laXMfTQLf6T2KVvXn/view?usp=sharing

     

    Let me know if it works!

     

    LC

    Interested in Power BI templates? Check my blog at www.finance-bi.com

     

    • NBOnecall's avatar
      NBOnecall
      Helper V
      6 years ago

      lc_financeIs there a trick to get the Year Month column in the matrix to be in the correct order. When I create it on my workbook it has it out of order.

       

      Thank you,

      Noel