Forum Discussion
Cumulative Totals for Map Visualization
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
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
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