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

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

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
Sept PBI Carousel

Power BI Monthly Update - September 2024

Check out the September 2024 Power BI update to learn about new features.

September Hackathon Carousel

Microsoft Fabric & AI Learning Hackathon

Learn from experts, get hands-on experience, and win awesome prizes.

Sept NL Carousel

Fabric Community Update - September 2024

Find out what's new and trending in the Fabric Community.

Top Kudoed Authors