How to: Display Daily Data in a Calendar View

When you are working with Daily Data it will sometimes make sense to display the information in a Calendar view. Here you will find a way to tweak a Grid View into the look and feel of a calendar.

Tweaking a grid into calendar view

First let us give you instructions on how to tweak the grid view – below you will also find a video that shows the steps life in a Data Model. The steps for creating a calendar view:

In the Model:

1. Create a measure for day of month (Max)

2. Create a measure for week number (Max)

3. Create a measure for weekday number (max)

4. Create a measure for date (max)

In Formulate:

1. Create a measure called “checkers for calendar” with the following formula (you can use the wizard, no need to code):

   ([Measue: Week] + [Measure:Day Of Month]) %2 -0.5 +
   If(DateFormat(Date(),”YYYY-MM-dd”) = left([Measure: Date],10)
   ,3,0)

   
   What the first row in this measure does, is it takes the modulo 2 of the week and the day – A Modulo operator returns the integer remainder of a division (7%2 = 1, 6%2 = 0).
   So the first part of this calculation sums the week and the day (Tuesday week 34 is 36) and returns the remainder from division by 2 - which is either 1 or 0. This will make each month the "checkers board" look. we reduce 0.5 so it's either -0.5 or +0.5.
   The second part is checking weather the current date (calculated from the date() function) is the date of the current cell (which the left 10 characters of the date measure) - thus Today will get the value 3.5 or 4.5.

In Discover:

1. Create a grid
2. Put the weekday on the columns
3. Put the week on the rows
4. Add the day of month measure
5. Add additional measures you want to show
6. Put the values chip on the rows.
7. Select the day of month measure, and align left, and bold
8. Other formatting as desired
9. Add the checkers measure to the color pane – set color to fixed,
   select 3 bands, -1 to 0, 0 to 1, 1 to 5.
10. Remove gridlines
11. Remove row headers
12. Minimize rows width
13. Set rows text and background to transparent
14. Remove readability and auto colorize.
15. You can make further changes as you wish.

Tips & Tricks Video

To make this even easier. Please find this solution in a short video:

 

If  you have any suggestion or feedback. Please Comment below.