Setting date ranges within measure

[gambleave]

I have a measure in the syntax below to count the number of distinct activities (using another distinctcount measure) in the preceding 12 months from today's date. How might a measure like this be adapted to calculate this value at the end of any given month? For example, I would like to be able to create a bar chart visual by month where this count for the preceding 12 months is taken from the last date of the month. 

Thank you!

12M Distinct Activity=

VAR endDate =TODAY()

VAR startDate=EDATE(endDate, -12)

RETURN

CALCULATE([Distinct Activity],

ALL(ServiceDetails[Activity Date],

(ServiceDetails[Activity Date] >= startDate

&& ServiceDetails[Activity Date] <= endDate

))

[v-stephen-msft · Accepted solution]

Hi @gambleave ,

You can create a [End of Month] column first.

The main table may have incomplete dates, so a calendar table is required.

Calendar = CALENDAR(DATE(2022,1,1),DATE(2022,12,31))

End of Month = ENDOFMONTH('Calendar'[Date])

Then create a measure to count.

Count =
CALCULATE (
    DISTINCTCOUNT ( 'Table'[Activity ID] ),
    FILTER (
        ALL ( 'Table' ),
        [Date] = [End of Month]
            && [YearMonth] = MAX ( 'Table'[YearMonth] )
    )
)

Best Regards,

Stephen Tao

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.