Cumulative Totals for Map Visualization

[dnaman]

Hi All,

My data set is logon activity (for an application) by month by office

Columns are:

LogonDate (datetime)

Reporting Office (i.e. <cityname> Toronto, Los Angeles, etc)

I would like to show the progression of logon activity from the beginning of my dataset (2016) to current (2018), using a slider (Timeline perhaps), and as i slide across by month, the circles representing the total count of LogonActivity in a location gets larger.

So it would show how the activity/usage of this application has grown over the past 15-16 months by location.

I've tried to use the Map visualization and the Timeline slicer and the only way i can 'hack' this, is if i hold SHIFT down while starting at the first date, and then clicking the subsequent month one at a time.

Is there a better way to show this? Can it be animated or automated?

Thanks in advance.

[v-yuezhe-msft · Accepted solution]

@dnaman,

I create a sample table named Logon, firstly create a Date column and count column using DAX below in the table.

Date = DATE(YEAR(Logon[LogonDate]),MONTH(Logon[LogonDate]),DAY(Logon[LogonDate]))
Count = CALCULATE(COUNTA(Logon[Reporting Office]))


Secondly, create a calendar table using DAX below. Create relationship between the calendar table and Logon table using date field.

Date = CALENDAR(DATE(2016,1,1),DATE(2018,12,31))

Thirdly, create the following measure to calculate cummulative count of logon activity in  the Logon  table.
cummulative = CALCULATE(SUM(Logon[Count]),FILTER(ALL('Date'),'Date'[Date]<=MAX('Date'[Date])))

Create a map visual as shown in the following screenshot. You still need  to click Month in the timeline slicer to filter the map, but don't need to hold SHIFT to select all months to calculate cummulative value in map.


Regards,
Lydia

[clawre1]

Hi 

I am having the same problem and was hoping to use your solution, but creating the date column i get the error "A single value for column x cannot be determined"

Any ideas on how to solve this? 

Thanks 

Chris

[dnaman]

@v-yuezhe-msft that works! The only grip i have now is that the size of the circles do not really 'grow' or scale relative to the other circles. Meaning the size of the circle for cumulative value 10 is basially the same cirzle size for cumulative value 100