Display only month-end partitions within your time series charts

Use-case

When displaying time-series data your Visual may become too dense with data points, especially if you’re displaying several year’s worth of data by day.

Displaying only the month-end values is one way to help reduce the data density within your charts. Daily partitioning allows you to match against your partition date values to only select the partitions that fall on the last day of each month.

Solution

Step 1: Calculate month-end values date values for date column

Example month-end date value calculation:

(case when month([order_date]) = 1 then concat(cast(year([order_date]) as string),'-',if(month([order_date]) < 10, '0', ''),cast(month([order_date]) as string),"-","31") when month([order_date]) = 2 then concat(cast(year([order_date]) as string),'-',if(month([order_date]) < 10, '0', ''),cast(month([order_date]) as string),"-","28") when month([order_date]) = 3 then concat(cast(year([order_date]) as string),'-',if(month([order_date]) < 10, '0', ''),cast(month([order_date]) as string),"-","31") when month([order_date]) = 4 then concat(cast(year([order_date]) as string),'-',if(month([order_date]) < 10, '0', ''),cast(month([order_date]) as string),"-","30") when month([order_date]) = 5 then concat(cast(year([order_date]) as string),'-',if(month([order_date]) < 10, '0', ''),cast(month([order_date]) as string),"-","31") when month([order_date]) = 6 then concat(cast(year([order_date]) as string),'-',if(month([order_date]) < 10, '0', ''),cast(month([order_date]) as string),"-","30") when month([order_date]) = 7 then concat(cast(year([order_date]) as string),'-',if(month([order_date]) < 10, '0', ''),cast(month([order_date]) as string),"-","31") when month([order_date]) = 8 then concat(cast(year([order_date]) as string),'-',if(month([order_date]) < 10, '0', ''),cast(month([order_date]) as string),"-","31") when month([order_date]) = 9 then concat(cast(year([order_date]) as string),'-',if(month([order_date]) < 10, '0', ''),cast(month([order_date]) as string),"-","30") when month([order_date]) = 10 then concat(cast(year([order_date]) as string),'-',if(month([order_date]) < 10, '0', ''),cast(month([order_date]) as string),"-","31") when month([order_date]) = 11 then concat(cast(year([order_date]) as string),'-',if(month([order_date]) < 10, '0', ''),cast(month([order_date]) as string),"-","30") when month([order_date]) = 12 then concat(cast(year([order_date]) as string),'-',if(month([order_date]) < 10, '0', ''),cast(month([order_date]) as string),"-","31") else "" end)

Storing the calculation at the Dataset level will make it easier to re-use it later throughout your Visuals.

Step 2: Filter your Visual to show only the dates that match the month-end date values (from previous step):

30%20PM

Final Result

Before:

After:

1 Like