Forum Discussion
Solved
Formula question - Counting number of visits completed in a data range by month
I am working, actually learning Power BI, and want to create a scorecard by month of "completed and not completed visits by month". Problem is that there are multiple date range for these visits. Ex...
Better to have some sample, but in any case working with data model you need to have the calendar for such calculation which covers all possible dates. These days usual way to create it is Power Query, you may find lot of samples, here is simple table.
With it set relationships with main table on date
As variant measures could be
Visits Completed:=CALCULATE( COUNTROWS(Table1), NOT ISBLANK(Table1[Completed]), USERELATIONSHIP('Date'[Date],Table1[Completed]) ) Visits Assigned:=COUNTROWS(Table1) Visits not completed:=CALCULATE( COUNTROWS(Table1), ISBLANK(Table1[Completed]) )More in attached file.
Better to have some sample, but in any case working with data model you need to have the calendar for such calculation which covers all possible dates. These days usual way to create it is Power Query, you may find lot of samples, here is simple table.
With it set relationships with main table on date
As variant measures could be
Visits Completed:=CALCULATE(
COUNTROWS(Table1),
NOT ISBLANK(Table1[Completed]),
USERELATIONSHIP('Date'[Date],Table1[Completed])
)
Visits Assigned:=COUNTROWS(Table1)
Visits not completed:=CALCULATE(
COUNTROWS(Table1),
ISBLANK(Table1[Completed])
)More in attached file.