Sep 07 2020 09:14 PM
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. Example - 150 visits are due - the start date is from 8/31 through 9/4 #2 has 200 visits with a start date of 8/24 and ends 9/11. On 9/2 I need to know - how many visits are completed in August, How many are completed in Sept and how many still need to be done. The completed column only has a data and time entry once a visit has been completed - otherwise blank. I started with a "status" column that told me if the visit was completed or incomplete. Next step is to break down months, I guess?
I have figured this all out in excel in pivot tables - however, I have been manipulating the data and using Vlookups to pull the info off the pivots. trying to get this automated to save me that time.
Thoughts?
Sep 08 2020 01:42 AM
SolutionBetter 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.
Sep 08 2020 01:42 AM
SolutionBetter 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.