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 ,

 

 

could you share a sample Power BI file?

 

You can share a file using DropBox, One Drive, Drive or any similar tool

 

LC

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