Count of Months with Data

[DanielReinman]

I have a need to dynamically calculate the number of months for which data exists, using a measure.

In its simplest form, I have a Calendar Table, Physician Table, and an Appointment Table. 

Depending on the dates selected or visible in a visual, I need the measure to be able to tell me how many months have data. 

i.e

To illustrate, If the data was put into a matrix, like the one below, and slices limited the page to just Jan-March, the measure would be able to tell me that Physician A has 2 months with data, Physician B has 3 months of data, etc. 

- but if I were to adjust the slicers to show more months, the measure results would change accordingly, such as below, the measure would now calculate 4 months of data for Physician A:

Icing on the cake would be to add a condition, such as Physician D has X number of months with data where CPT Code = Y.

[MURTAZA · Accepted solution]

The simplest solution might be just to do a DISTINCTCOUNT of months. I am guessing your appointment table has the dates. Just use that date to extract month and do a distinctcount on the months

[DanielReinman]

Wow - yeah, I was really overthinking this. Works like a charm!

[MURTAZA]

On second thought, you may wanna do DISTINCTCOUNT on MONTH-YEAR (03-2023), because if you analyze the data over multiple years, DISTINCTCOUNT on just Month wouldn't work.