Cumulative Total where zero value are filled with values from previous periode

[Anonymous]

Hi All,

hope to get help for the following problem.

I have a two tables which includes a date, uniqueDevices and activeDevices, there is also a dedicated date table. I want to get a cumulative Total of the activeDevices. The problem I have, for some Countries the activeDevices are the same for couple of weeks and it´s showing Null values in the graph as well as in the table:

Week_Year	#Unique Devices	ActiveDevices_rollingE
2018-4	16	32
2018-5	20	35
2018-6	14
2018-7	10
2018-8	7
2018-9	9
2018-10	15	37
2018-11	8	38
2018-12	22	66
2018-13	19	76
2018-14	8	88

The formula I´m using is:

ActiveDevices_rollingE = CALCULATE (
SUMX(MeasuresTableAirwatch; MeasuresTableAirwatch[ActiveDevices]);
FILTER(ALL(Period[date]);
Period[Date] <= MAX (Airwatch[AppEnrollmentDate])))

I need to have 35 active Devices also in line 2018-6, 2018-7, 2018-8. 

Thanks in advance

Best

Lina

[Anonymous]

First off, i've optimised your calculated column:

ActiveDevices_rollingE = var endDate = LASTDATE(Airwatch[AppEnrollmentDate])
RETURN
CALCULATE (
	SUM(MeasuresTableAirwatch[ActiveDevices]);	
	ALL(Period[date]);
	Period[Date] <= endDate	
)

Doing this has made me notice that you are getting the last date from the Airwatch table, if you have a table relationship with this you could be fall foul of the context of that join.  I would reconsider that portion next.

[Anonymous]

Thanks for your answer, the result is the same (missing values for the three weeks). If I delete the relationship between the date table and the AppEnrollmentDate I get the value (88 ActiveDevices) of the last AppEnrollmentDate for all previous weeks. Any other suggestions to solve the problem? Thanks Lina

[Anonymous · Accepted solution]

Yes, try this instead.  (without deleting the relationship)

ActiveDevices_rollingE = var endDate = CALCULATE(
	LASTDATE(Airwatch[AppEnrollmentDate]),
	ALL('Airwatch')
)
RETURN
CALCULATE (
	SUM(MeasuresTableAirwatch[ActiveDevices]);	
	ALL(Period[date]);
	Period[Date] <= endDate	
)