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

@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 Group Leader_768x460.jpg

Manage your user group events

Check out the News & Announcements to learn more.

Welcome Super Users.jpg

Super User Season 2

Congratulations, the new Super User Season 2 for 2021 has started!

Community Connections 768x460.jpg

Community & How To Videos

Check out the new Power Platform Community Connections gallery!

Teds Dev Camp Oct. 2021 768x460.jpg

Power BI Dev Camp - October 28th, 2021

Mark your calendars and join us for our next Power BI Dev Camp!