Counting from start month-end month: First time didn't work

[alpeytongreene]

I am working on a contract labor dashboard, I need to count the person in contract from their start date through their end date- without having to create multiple entries per each labor.

Example:

Bob Smith started 01-01-2021 and ends July 30, 2021- so Bob would count January, February, March, April, May, June, and July 

Sally Sue started February 15 and ends May 15- so Sally would count February, March, April, and May. 

I have the formula set as: 

Count =

VAR Tab =

FILTER('CL Labor Count Table',

'CL Labor Count Table'[Start Date]<= MAX('Date Table'[Date])

&& 'CL Labor Count Table'[End Date]>= MIN('Date Table'[Date])

)

RETURN

CALCULATE(COUNT('CL Labor Count Table'[Lawson ID]), Tab)

but this is  only showing to the start month. HELP! 

[lbendlin]

the last measure code I posted should be used in its entirety. Can't really pick it apart for what you are trying to achieve.

[alpeytongreene]

I created the table based off yours, added the additional columns. I even tried duplicating my date tables for each start and end date and inserting those into the formula to see if I could get a different visual. I removed all connection to what I have called the test table and even tried using an active flag scenario. 

[lbendlin]

is that your only line of code?  A calculated column and a measure need to return a single (scalar) value.  You can use the code as it is, but only for a calculated table.

[alpeytongreene]

I did create a table based on the table you had sent below.  (I couldn't post the formula it said I was flooding).

[lbendlin]

Please show the DAX code where you hit that issue.

[alpeytongreene]

[lbendlin]

For this exercise you need a disconnected date table, and it needs a Month Number column (year*100+month). I am not sure how to do this when the date table is connected.

EDIT:  Never mind. Simply adding ALL() did the trick. Still need the Month Number column though.

Count = 
var m = SELECTEDVALUE('Date Table'[MYS])
var l  = ADDCOLUMNS(ALL('Contract Labor'),"ct",if(YEAR('Contract Labor'[Start Date])*100+MONTH('Contract Labor'[Start Date])<=m && YEAR('Contract Labor'[End Date])*100+MONTH('Contract Labor'[End Date])>=m,1,0))
return sumx(l,[ct])

[lbendlin]

I think I understand now.  For this execise I created a sample calendar calculated table (MYS is used to sort MY)

Date Table = ADDCOLUMNS(CALENDAR(min('Contract Labor'[Start Date]),max('Contract Labor'[End Date])),"MY",FORMAT([Date],"MMM YYYY"),"MYS",YEAR([Date])*100+MONTH([Date]))

 Note: The Date Table should not be connected to the Contract Labor table.

Here's your measure. 

Count = 
var m = SELECTEDVALUE('Date Table'[MYS])
var l  = ADDCOLUMNS('Contract Labor',"ct",if(YEAR('Contract Labor'[Start Date])*100+MONTH('Contract Labor'[Start Date])<=m && YEAR('Contract Labor'[End Date])*100+MONTH('Contract Labor'[End Date])>=m,1,0))
return sumx(l,[ct])

[alpeytongreene]

I've been trying to get this right and see if I can adjust for the answer but I keep getting the error message "The expression refers to multiple columns. Multiple columns cannot be converted to a scalar value"

[alpeytongreene]

I think it is close to a break through. Any possibility of a meeting to do a live demonstration? All I would need is your email and what date/time works best for you. Which TZ are you in?

I have the Date/MY table you created connected in my data, but it is only showing the start date. I am working on the outcome information now (since it's not done) 

[lbendlin]

I still don't get it.  You say "I would want to be able to display total numbers for each month (currently I have 494 active for March 2021)"  yet your desired outcome shows a list of month names.

Total numbers of what?

[alpeytongreene]

They person would need to count in each of those months based off their start date and end date. It is counting the time between essentially. 

Edited to add- I am putting this into a graph to show counts of contract labor over time if that gives a better context to track how much is being spent on contracted labor. 

[lbendlin]

Please revise your desired outcome example.

[alpeytongreene]

Table name is Contract Labor

Standardized Date table is Date Table

Lawson is the number identifier

There is the possibility of adding data from 2019, and 2020 as that will be formatted the same way. I would want to be able to display total numbers for each month (currently I have 494 active for March 2021)

Lawson     Start Date            End Date            Desired Outcome for the ID  to be counted in 2021:

123           1/1/2021              3/16/2021           Jan, Feb, Mar

456           2/1/2021              6/2/2021             Feb, Mar, Apr, May, Jun

789           2/15/2021            7/15/2021           Feb, Mar, Apr, May, Jun, Jul

987           3/1/2021             6/2/2021              Mar, Apr, May, Jun

654           3/17/2021           8/8/2021              Mar, Apr, May, Jun, Jul, Aug

321          3/29/2021            5/2/2021              Mar, Apr, May

246          12/4/2020            4/2/2021              Dec 2020, Jan, Feb, Mar, Apr

[lbendlin]

Do you need the month name or the month number?

[alpeytongreene]

I would need month and year.

[lbendlin]

Please provide sample data in usable format (not as a picture) and show the expected outcome.

[alpeytongreene]

Examples

Lawson     Start Date            End Date            Desired Outcome

123           1/1/2021              3/16/2021           Count for Jan, Feb, Mar

456           2/1/2021              6/2/2021             Count for Feb, Mar, Apr, May, Jun

789           2/15/2021            7/15/2021           Count for Feb, Mar, Apr, May, Jun, Jul

987           3/1/2021             6/2/2021              Count for Mar, Apr, May, Jun

654           3/17/2021           8/8/2021              Count for Mar, Apr, May, Jun, Jul, Aug

321          3/29/2021            5/2/2021               Count for Mar, Apr, May

I do have a date table separate from this as well. 

[lbendlin]

you need to protect your date table date range from the filter context transition. Use variables.

What is "[Lawson ID]" ? Didn't you say you want to count months?

[alpeytongreene]

How would I do that exactly? I am still relatively new to powerBI and I am trying to learn as fast as I can.