Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
My end goal is to calculate rolling 12 month inventory average. I'm having problems calculating the rolling 12 month sum of my inventory balance due to the format of my data source.
In January of every year, I have a starting inventory balance and on the following months I have only the inventory movements. (Highlighted in green are the inventory balances for years 2016 and 2017).
I was able to calculate the inventory balance for each month using the YTD formula
YTD Inventory = CALCULATE([Total Inventory], DATESYTD('Calendar'[Date]))
Total Inventory = SUM(Inventory[Inventory])
Solved! Go to Solution.
Hi, try with this:
Step 1: A measure:
SumInventoryThisYear = VAR _Fecha = SELECTEDVALUE ( Table1[Fecha] ) RETURN CALCULATE ( SUM ( Table1[Inventario] ), FILTER ( ALL ( Table1 ), Table1[Fecha] <= _Fecha && YEAR ( Table1[Fecha] ) = YEAR ( _Fecha ) ) )
2. Rolling Measure:
Rolling = VAR _Fecha = SELECTEDVALUE ( Table1[Fecha] ) VAR _Fecha_12MonthsBefore = EDATE ( SELECTEDVALUE ( Table1[Fecha] ), -12 ) VAR _Rolling = SUMX ( FILTER ( ALL ( Table1 ), Table1[Fecha] <= _Fecha && Table1[Fecha] > _Fecha_12MonthsBefore ), [SumInventoryThisYear] ) RETURN _Rolling
Regards
Victor
There is a rolling average Quick Measure, have you looked at that?
Just tried it but can't use Quick Time Intelligence measure because I'm using an extrenal Calendar Table.
But I think it would have the same problem as I currently have. It would average the inventory movements not the inventory monthly balance.
I think I need to save the final monthly inventory balance somewhere so I can use my rolling sum measure on that result, but I have no idea how to go about it.
Hi, try with this:
Step 1: A measure:
SumInventoryThisYear = VAR _Fecha = SELECTEDVALUE ( Table1[Fecha] ) RETURN CALCULATE ( SUM ( Table1[Inventario] ), FILTER ( ALL ( Table1 ), Table1[Fecha] <= _Fecha && YEAR ( Table1[Fecha] ) = YEAR ( _Fecha ) ) )
2. Rolling Measure:
Rolling = VAR _Fecha = SELECTEDVALUE ( Table1[Fecha] ) VAR _Fecha_12MonthsBefore = EDATE ( SELECTEDVALUE ( Table1[Fecha] ), -12 ) VAR _Rolling = SUMX ( FILTER ( ALL ( Table1 ), Table1[Fecha] <= _Fecha && Table1[Fecha] > _Fecha_12MonthsBefore ), [SumInventoryThisYear] ) RETURN _Rolling
Regards
Victor
Thank you Victor! That worked perfectly 🙂
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
115 | |
100 | |
88 | |
68 | |
61 |
User | Count |
---|---|
152 | |
120 | |
102 | |
87 | |
68 |