Forum Discussion

gambleave's avatar
gambleave
Helper II
4 years ago
Solved

Setting date ranges within measure

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
))
Need Help
Tutorial Requests
  • Anonymous's avatar
    Anonymous
    4 years ago

    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.

1 Reply

  • Anonymous's avatar
    Anonymous
    Not applicable
    4 years ago

    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.