Forum Discussion

Luke_Howells's avatar
Luke_Howells
Helper I
5 years ago
Solved

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 h...
Data Protection
Need Help
  • v-cazheng-msft's avatar
    v-cazheng-msft
    5 years ago

    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.

     

amitchandak's avatar
amitchandak
Super User
5 years ago

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

 

example

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