Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
8 years ago
Solved

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

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 DevicesActiveDevices_rollingE
2018-41632
2018-52035
2018-614 
2018-710 
2018-87 
2018-99 
2018-101537
2018-11838
2018-122266
2018-131976
2018-14888

 

 

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

Need Help
  • Anonymous's avatar
    Anonymous
    8 years ago

    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	
)

3 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    8 years ago

    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's avatar
      Anonymous
      Not applicable
      8 years ago

      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's avatar
        Anonymous
        Not applicable
        8 years ago

        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	
)