Distinct Count on a Rolling Month

[Luke_Howells]

Hi All,

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. 

[v-cazheng-msft · Accepted solution]

Hi @Luke_Howells 

You can create a Measure like this:

AccumulateAuditSiteByMonth =

VAR res =

    CALCULATE (

        [CountAuditSite],

        FILTER ( ALL ( 'dataTable' ), 'dataTable'[Date] <= MAX ( 'dataTable'[Date] ) ),

        FILTER (

            ALLSELECTED ( dateTable ),

            dateTable[Start of Month] = MAX ( dateTable[Start of Month] )

        )

    )

RETURN

    IF ( HASONEFILTER ( dateTable[Start of Month] ), res, [CountAuditSite] )

The result looks like this:

For more details, you can refer the attached pbix file.

Best Regards

Caiyun Zheng

Is that the answer you're looking for? If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Luke_Howells]

@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?

[amitchandak]

@Luke_Howells , to me it seems like YTD with an end date of March 

example

YTD Sales = CALCULATE([measure],DATESYTD('Date'[Date],"3/31"))