Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started
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:
Check out the September 2024 Power BI update to learn about new features.
Learn from experts, get hands-on experience, and win awesome prizes.
User | Count |
---|---|
112 | |
95 | |
92 | |
35 | |
29 |