Skip to main content
cancel
Showing results for
Search instead for
Did you mean:

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

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:

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.

1 ACCEPTED SOLUTION
Community Support

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.

3 REPLIES 3
Community Support

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.

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?

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"))

## Helpful resources

Announcements

#### Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

#### Join our Community Sticker Challenge

If you love stickers, then you will definitely want to check out our Community Sticker Challenge!

#### Power BI Monthly Update - July 2024

Check out the July 2024 Power BI update to learn about new features.

#### Fabric Community Update - July 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors
Top Kudoed Authors