March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Early bird discount ends December 31.
Register NowBe one of the first to start using Fabric Databases. View on-demand sessions with database experts and the Microsoft product team to learn just how easy it is to get started. Watch now
Hello everybody! Hope you're doing great!
I'm here to get some assistance from the community.
I'm working on a Beginning of Month matrix and I'm having some difficulties to do the cumulative sum of a measure and of a column.
This is how my table looks right now:
Values in yellow are columns, the rest are measures.
The highlighted rows are a succesful cumulative sum of a sales plan. I'm trying to replicate this, but with BOM Plan (measure) or BOM Plan 3 (column).
,But when I try to do the same with BOM Plan or BOM Plan 3, the sum is incorrect.
If BOM Plan on January is:
780,063.01
and BOM Plan on February is:
783,636.31
The cumulative sum for february is expected to be: 780,063.01 + 783,636.31 = 1,563,699.32
And the formulas is giving me 1,135,346.57
So, thats incorrect.
What I noticed, and I really don't understand why, is that the formulas have the correct value of BOM Plan for January (780,063.01) but the formula is doing a cumulative sum of January's BOM Plan and February's Plan
So SumaInv or BOM Plan running total is correct on January (780,063.01) but in February somehow is doing the sum of 780,063.01 and the Plan of February (355,283.55) to a total of = 1,135,346.56
But again, that's not correct. I'm glad if someone has an Idea of how to get that number correctly.
BOM Plan measure:
BOM Plan =
VAR __MonthPlan = [Plan] -- Assuming this is a measure which is sum(Table[Plan])
VAR __AnnualPlan = AVERAGE(Dashboard[Total Plan Year])
VAR __Rot = SELECTEDVALUE(Dashboard[Rot], 0) -- Use SELECTEDVALUE to get the value from the "Rot" column
VAR __Result = (__MonthPlan + ( __AnnualPlan * (( 1 / __Rot) - 1 / 12) ))
RETURN
__Result
BOM Plan 3 column:
BOM Plan 3 = Dashboard[BOM Plan 2] + (Dashboard[Total Plan Year] * ((1/Dashboard[Rot])-(1/12)))
SumaINV measure:
SumaInv =
VAR CurrentMonth = MAX('Dashboard'[Month])
RETURN
CALCULATE(
[BOM Plan], -- Reference the "BOM Plan" measure directly
FILTER(
ALL('Dashboard'),
'Dashboard'[Month] <= CurrentMonth && YEAR('Dashboard'[Fecha Venta]) = 2022
)
)
BOM Plan running total in month (Quick measure)
BOM Plan running total in Month =
CALCULATE(
[BOM Plan],
FILTER(
CALCULATETABLE(
SUMMARIZE(
'Dashboard',
'Dashboard'[Fecha Venta].[MonthNo],
'Dashboard'[Fecha Venta].[Month]
),
ALLSELECTED('Dashboard')
),
ISONORAFTER(
'Dashboard'[Fecha Venta].[MonthNo], MAX('Dashboard'[Fecha Venta].[MonthNo]), DESC,
'Dashboard'[Fecha Venta].[Month], MAX('Dashboard'[Fecha Venta].[Month]), DESC
)
)
)
Thank you!!
@HectorMSC Try this:
March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount!
Your insights matter. That’s why we created a quick survey to learn about your experience finding answers to technical questions.
Arun Ulag shares exciting details about the Microsoft Fabric Conference 2025, which will be held in Las Vegas, NV.
User | Count |
---|---|
124 | |
89 | |
84 | |
70 | |
51 |
User | Count |
---|---|
206 | |
143 | |
97 | |
79 | |
68 |