SOLVED

Formula question - Counting number of visits completed in a data range by month

%3CLINGO-SUB%20id%3D%22lingo-sub-1642867%22%20slang%3D%22en-US%22%3EFormula%20question%20-%20Counting%20number%20of%20visits%20completed%20in%20a%20data%20range%20by%20month%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1642867%22%20slang%3D%22en-US%22%3E%3CP%3EI%20am%20working%2C%20actually%20learning%20Power%20BI%2C%20and%20want%20to%20create%20a%20scorecard%20by%20month%20of%20%22completed%20and%20not%20completed%20visits%20by%20month%22.%20Problem%20is%20that%20there%20are%20multiple%20date%20range%20for%20these%20visits.%26nbsp%3B%20Example%20-%20150%20visits%20are%20due%20-%20the%20start%20date%20is%20from%208%2F31%20through%209%2F4%20%232%20has%20200%20visits%20with%20a%20start%20date%20of%208%2F24%20and%20ends%209%2F11.%20On%209%2F2%20I%20need%20to%20know%20-%20how%20many%20visits%20are%20completed%20in%20August%2C%20How%20many%20are%20completed%20in%20Sept%20and%20how%20many%20still%20need%20to%20be%20done.%20The%20completed%20column%20only%20has%20a%20data%20and%20time%20entry%20once%20a%20visit%20has%20been%20completed%20-%20otherwise%20blank.%20I%20started%20with%20a%20%22status%22%20column%20that%20told%20me%20if%20the%20visit%20was%20completed%20or%20incomplete.%20Next%20step%20is%20to%20break%20down%20months%2C%20I%20guess%3F%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20have%20figured%20this%20all%20out%20in%20excel%20in%20pivot%20tables%20-%20however%2C%20I%20have%20been%20manipulating%20the%20data%20and%20using%20Vlookups%20to%20pull%20the%20info%20off%20the%20pivots.%20trying%20to%20get%20this%20automated%20to%20save%20me%20that%20time.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThoughts%3F%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-1642867%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EBI%20%26amp%3B%20Data%20Analysis%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EFormulas%20and%20Functions%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EPower%20BI%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1643441%22%20slang%3D%22en-US%22%3ERe%3A%20Formula%20question%20-%20Counting%20number%20of%20visits%20completed%20in%20a%20data%20range%20by%20month%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1643441%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F691896%22%20target%3D%22_blank%22%3E%40Justin_Yost%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EBetter%20to%20have%20some%20sample%2C%20but%20in%20any%20case%20working%20with%20data%20model%20you%20need%20to%20have%20the%20calendar%20for%20such%20calculation%20which%20covers%20all%20possible%20dates.%20These%20days%20usual%20way%20to%20create%20it%20is%20Power%20Query%2C%20you%20may%20find%20lot%20of%20samples%2C%20here%20is%20simple%20table.%3C%2FP%3E%0A%3CP%3EWith%20it%20set%20relationships%20with%20main%20table%20on%20date%3C%2FP%3E%0A%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20image-alt%3D%22image.png%22%20style%3D%22width%3A%20587px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F216980i97CAF59056FC1EB3%2Fimage-size%2Flarge%3Fv%3D1.0%26amp%3Bpx%3D999%22%20title%3D%22image.png%22%20alt%3D%22image.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%0A%3CP%3EAs%20variant%20measures%20could%20be%3C%2FP%3E%0A%3CPRE%20class%3D%22lia-code-sample%20language-excel%22%3E%3CCODE%3EVisits%20Completed%3A%3DCALCULATE(%0A%20%20%20%20COUNTROWS(Table1)%2C%0A%20%20%20%20NOT%20ISBLANK(Table1%5BCompleted%5D)%2C%0A%20%20%20%20USERELATIONSHIP('Date'%5BDate%5D%2CTable1%5BCompleted%5D)%0A%20)%0A%0AVisits%20Assigned%3A%3DCOUNTROWS(Table1)%0A%0AVisits%20not%20completed%3A%3DCALCULATE(%0A%20%20COUNTROWS(Table1)%2C%0A%20%20ISBLANK(Table1%5BCompleted%5D)%0A%20)%3C%2FCODE%3E%3C%2FPRE%3E%0A%3CP%3EMore%20in%20attached%20file.%3C%2FP%3E%3C%2FLINGO-BODY%3E
New Contributor

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? 

1 Reply
Best Response confirmed by Justin_Yost (New Contributor)
Solution

@Justin_Yost 

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

image.png

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.