4 Replies Latest reply on May 19, 2017 6:41 AM by Maurice Esperanza

# Looking to get the number of records for the last day of each month

For the example below I would like to just get the number of records for 7/31/2016 = 130 and 8/31/2016  = 161

 Date Number of Records 7/1/2016 100 7/2/2016 101 7/3/2016 102 7/4/2016 103 7/5/2016 104 7/6/2016 105 7/7/2016 106 7/8/2016 107 7/9/2016 108 7/10/2016 109 7/11/2016 110 7/12/2016 111 7/13/2016 112 7/14/2016 113 7/15/2016 114 7/16/2016 115 7/17/2016 116 7/18/2016 117 7/19/2016 118 7/20/2016 119 7/21/2016 120 7/22/2016 121 7/23/2016 122 7/24/2016 123 7/25/2016 124 7/26/2016 125 7/27/2016 126 7/28/2016 127 7/29/2016 128 7/30/2016 129 7/31/2016 130 8/1/2016 131 8/2/2016 132 8/3/2016 133 8/4/2016 134 8/5/2016 135 8/6/2016 136 8/7/2016 137 8/8/2016 138 8/9/2016 139 8/10/2016 140 8/11/2016 141 8/12/2016 142 8/13/2016 143 8/14/2016 144 8/15/2016 145 8/16/2016 146 8/17/2016 147 8/18/2016 148 8/19/2016 149 8/20/2016 150 8/21/2016 151 8/22/2016 152 8/23/2016 153 8/24/2016 154 8/25/2016 155 8/26/2016 156 8/27/2016 157 8/28/2016 158 8/29/2016 159 8/30/2016 160 8/31/2016 161
• ###### 1. Re: Looking to get the number of records for the last day of each month

Maurice,

This is a case where a FIXED Level of Detail calculation can work really well as a filter.

If you create a calculated field like this:

[Date] = {FIXED DATETRUNC('month', [Date]) : MAX([Date])}

And filter to keep only true values then you'll have the last day of each month.  In some versions of Tableau, you may have to create DATETRUNC('month', [Date]) as a separate calculation and then use that calculation in the code above.

Hope that helps!

Joshua

• ###### 3. Re: Looking to get the number of records for the last day of each month

Joshua,

I had to break it into three steps but overall your method worked.

• ###### 4. Re: Looking to get the number of records for the last day of each month

Sherzodbek,

Thank you for the prompt reply but I wasn't able to open the sample work book because I am running with an older version of Tableau.

Regards,

Maurice