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

Reply
DanielReinman
Frequent Visitor

Count of Months with Data

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. 

 

DanielReinman_0-1693945411468.png

- 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:

DanielReinman_1-1693946017895.png

 

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.

1 ACCEPTED SOLUTION
MURTAZA
Resolver I
Resolver I

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

View solution in original post

3 REPLIES 3
MURTAZA
Resolver I
Resolver I

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

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

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. 

Helpful resources

Announcements
July 2024 Power BI Update

Power BI Monthly Update - July 2024

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

Top Solution Authors