cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Luke_Howells
Helper I
Helper I

Distinct Count on a Rolling Month

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:

 

Luke_Howells_0-1618502797643.png

 

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. 

 

Luke_Howells_1-1618503124943.png

 

 

 

1 ACCEPTED SOLUTION
v-cazheng-msft
Community Support
Community Support

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:

v-cazheng-msft_0-1618811910709.png

 

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.

 

View solution in original post

3 REPLIES 3
v-cazheng-msft
Community Support
Community Support

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:

v-cazheng-msft_0-1618811910709.png

 

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.

 

View solution in original post

Luke_Howells
Helper I
Helper I

@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
Super User IV
Super User IV

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

 

example

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



Did I answer your question? Mark my post as a solution! Appreciate your Kudos!!
Dashboard of My Blogs !! YouTube Channel !! Connect on Linkedin

Proud to be a Super User!

Helpful resources

Announcements
PBI User Groups

Welcome to the User Group Public Preview

Check out new user group experience and if you are a leader please create your group!

MBAS on Demand

Microsoft Business Applications Summit sessions

On-demand access to all the great content presented by the product teams and community members! #MSBizAppsSummit #CommunityRocks

MBAS Attendee Badge

Claim Your Badge & Digital Swag!

Check out how to claim yours today!

secondImage

Are You Ready?

Test your skills now with the Cloud Skills Challenge.

Top Solution Authors