Forum Discussion

bullius's avatar
bullius
Helper V
9 years ago

Using Running Total in Calculations

Hi, I have a measure that calculates the running total of no. of companies. I want to multiply this figure for a given year by another amount, e.g.    Year Running Total Amount Desired Res...
Need Help
v-jiascu-msft's avatar
v-jiascu-msft
Microsoft Employee
9 years ago

Hi bullius,

 

What is your formula of "Running Total"? Maybe you could try to modify the formula like this:

Running Total =
CALCULATE (
    COUNT ( 'table'[company] ),
    FILTER ( ALL ( 'table' ), 'table'[year] <= MAX ( 'table'[year] ) )
)

Best Regards!

Dale

bullius's avatar
bullius
Helper V
9 years ago

Hi,

 

Here is the formula for the Running Total:

 

Running Total = 
CALCULATE (
	DISTINCTCOUNT (
		Fact_OccasionsComp[CompanyID]
	),
	Fact_OccasionsComp[CompOccasion] = "Established",
	FILTER (
		ALLSELECTED (
			Fact_OccasionsComp
		),
		Fact_OccasionsComp[Date] <= MAX (
			Dim_Calendar[Date]
		) 
	)
) - 
CALCULATE (
	DISTINCTCOUNT (
		Fact_OccasionsComp[CompanyID]
	),
	Fact_OccasionsComp[CompOccasion] = "Closed",
	Fact_OccasionsComp[Date] = null
	),
	FILTER (
		ALLSELECTED (
			Fact_OccasionsComp
		),
		Fact_OccasionsComp[Date] <= MAX (
			Dim_Calendar[Date]
		)
	) 
)

It is basically the same as the one you suggested, but with a few differences ("Established" - "Closed", ALLSELECTED, rather than ALL etc...)