I have a table of data which contain a date column, site name column and an audit score column. What I want to do is be able to have a calculation that can show the number of sites that have been audited throughout each month on a rolling basis. The data table looks like:
I also have a date table which I use for a date filter. Each site may appear more than once per month which is why it needs to be a distinct count (or I think so). I have managed to work out how many sites have been audited on each date but want to have an acummalting column that would reset with each first day of the month (hence the first day column below). I was hoping the calculation would produce what is column EXAMPLE below:
NOTE: Column measure is what I have managed to write which works out how many sites have been audited on each date.
@amitchandak Thanks for the help but the data in the table goes back for a couple of years and ideally want the rolling total column not to haave a fixed date in there if possible? Hope that makes sense?