Running Total Measure

[Anonymous]

I am trying to create a running total measure - I've tried following a few guides but none have worked successfully.

I'm using a live connection to a tabular model - ideally this will be a measure in the tabular model but for now I've been testing by creating the measure in Power BI.

I have a table called 'Incidents' - this is used as my fact table. This has two measurescalled IncidentsLogged and IncidentsCompleted. I have a measure called DeltaLoggedToCompleted which subtracts the completed incidents from the logged incidents (the 2 measures above). This measure is working successfully. I then have a Dates table with a column called CalendarYearMonth (displays in the format 2019-01). The dates table has 2 inactive relationships from Dates[DateKey] to Incidents[LoggedKey] and Incidents[CompletedKey] respectively that contain values such as "20190101".

What I want is for a running total to be calculated as follows:

I have tried the following formulas with no luck:

RunningTotal1 = CALCULATE(SUM(Incidents[IncidentsLogged])-SUM(Incidents[IncidentsCompleted]),FILTER(ALLEXCEPT(Dates,Dates[CalendarYearMonth]),Dates[Date] <= SELECTEDVALUE (Dates[Date])))

RunningTotal2 = CALCULATE(SUM(Incidents[IncidentsLogged])-SUM(Incidents[IncidentsCompleted]),FILTER(ALLSELECTED(Dates),Dates[Date] <= MAX(Dates[Date])),USERELATIONSHIP(Incidents[LoggedKey],Dates[DateKey]))

RunningTotal3:=SUMX(FILTER(ALLSELECTED(Dates[Date]),Dates[Date]<=MAX(Dates[Date])),[DeltaLoggedToCompleted])

[Anonymous]

That's worked a charm, thank you for the super fast response

[Gopal30 · Accepted solution]

RunningTotal3:=SUMX(FILTER(ALLSELECTED(Dates[Date]),Dates[Date]<=MAX(Dates[Date])),[DeltaLoggedToCompleted])

Try to modify this one to:

RunningTotal3:=Calculate(SUMX('Table',Table[DeltaLoggedToCompleted]),(FILTER(ALL(Table[CalendarYearMonth]),Table[CalendarYearMonth]<=MAX(Table[CalendarYearMonth]))

[sowmya2553]

I am trying to achieve below with DAX to get cumilative running total every month as per region, country and city.

In power BI drill down option is enabled and I have to see the cumilative numbers as per the three level hirearchy.

with DAX it seems to work only city level but sometimes it also turns out to be incorrect as per below screenshot.

I badly need this to be worked. Please help

There are few blanks in country column from source file but I cant see any ticket number when I select only blank as filter.

		January	January	February	February	March	March	April	April
		Total Count	Cumil_count	Total Count	Cumil_count	Total Count	Cumil_count	Total Count	Cumil_count
Region	LATAM	5	5	4	9	3	11	2	14
Country	Belgium	2	2	2	4	1	5	1	6
Country	Brazil	3	3	2	5	2	7	1	8
City	SOROCABA	1	1	1	2	1	3	1	4
City	Sau Paulo	2	2	1	3	1	4		4

Tried this and it only gives same count as region for other two down levels,

measure = CALCULATE(DISTINCTCOUNT('SRMS Report'[INCIDENT NUMBER]),

FILTER(ALL('SRMS Report'),

'SRMS Report'[month]<=max('SRMS Report'[month]) && [Country]<=('SRMS Report'[Country]) && [City]<=('SRMS Report'[City]))

)

Main Formula is 

Cumulative_actual = CALCULATE([ticket count],

FILTER(ALL('SRMS Report'),

'SRMS Report'[month]<=max('SRMS Report'[month])),VALUES('Campus Staff Details'[Region]),VALUES('SRMS Report'[Country]),values('SRMS Report'[City])

)

This gives the cumilative count but not accurate

note* ticket count = count(srms report(incident number)

srms report(month) is a month number based on close date column

Please help