Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Be 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

Reply
HectorMSC
Frequent Visitor

Wrong Cumulative Sum of a Measure or Column

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:

HectorMSC_0-1695413063655.png

Values in yellow are columns, the rest are measures.

HectorMSC_1-1695413141979.png

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).

HectorMSC_2-1695413308199.png

,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

HectorMSC_3-1695413629968.png

 

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!!

1 REPLY 1
Greg_Deckler
Super User
Super User

@HectorMSC Try this:

https://community.fabric.microsoft.com/t5/Quick-Measures-Gallery/Better-Running-Total/m-p/2755666#M8...



Follow on LinkedIn
@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
Power BI Cookbook Third Edition (Color)

DAX is easy, CALCULATE makes DAX hard...

Helpful resources

Announcements
Las Vegas 2025

Join us at the Microsoft Fabric Community Conference

March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount!

Dec Fabric Community Survey

We want your feedback!

Your insights matter. That’s why we created a quick survey to learn about your experience finding answers to technical questions.

ArunFabCon

Microsoft Fabric Community Conference 2025

Arun Ulag shares exciting details about the Microsoft Fabric Conference 2025, which will be held in Las Vegas, NV.

December 2024

A Year in Review - December 2024

Find out what content was popular in the Fabric community during 2024.