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

     

NBOnecall's avatar
NBOnecall
Helper V
6 years ago

lc_finance 

 

I can't think of a real easy way to do this. It is tied into our database and has a ton of data in it. Trying to create a mini verision of it for one item seems like a large task, but if it is truley needed to solve my problem then I can attempt to do that.

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