Rolling Measure calculation

[NBOnecall]

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 display a matrix showing something like below.

GMROII

GMROII = 
DIVIDE(SUM('ns vwSalesRegisterByComponent'[GrossProfit]), [AverageInventory])

AverageInventory = sum('ns InventorySnapshot'[Value])/[Days]

Days = 
VAR a =
    FIRSTDATE (Dimtime[Date] )
VAR b =
    CALCULATE ( MAX ( Dimtime[Date] ), ALLSELECTED ( Dimtime[Date] ) )
RETURN
    DATEDIFF ( a, b, DAY )+1

Turns

Turns = DIVIDE(SUM('ns vwSalesRegisterByComponent'[ExtendedCost]), [AverageInventory])

[lc_finance]

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]

@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]

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

[NBOnecall]

@lc_finance 

https://www.dropbox.com/s/q438fxb6ogpp6nu/Test.pbix?dl=0

Let me know if this link works. As you can see I have a date slicer that I can pick the range of my dates. I would like to be able to output the 12 month dates in a matrix to show the change month by month always looking back a year.

Thank you!

[lc_finance · Accepted solution]

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]

@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

[lc_finance]

HI @NBOnecall ,

Yes, there is! You should choose the Year Month column, then go to Modeling -> Sort by column to custom sort it.

I just wrote a blog post about this: https://finance-bi.com/power-bi-learn-how-to-custom-sort-a-column/

Feel free to take a look at it, and let me know if anything is still unclear.

Regards,

LC