Forum Discussion
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 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 )+1Turns
Turns = DIVIDE(SUM('ns vwSalesRegisterByComponent'[ExtendedCost]), [AverageInventory])
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
7 Replies
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
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.
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
