topic Re: Counting from start month-end month: First time didn't work in DAX Commands and Tips

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

Anonymous — Thu, 25 Mar 2021 21:43:19 GMT

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!

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

lbendlin — Sat, 27 Mar 2021 03:22:57 GMT

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?

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

Anonymous — Mon, 29 Mar 2021 15:57:29 GMT

Yes it is counting each ID for the month.

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

Anonymous — Mon, 29 Mar 2021 19:37:36 GMT

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

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

lbendlin — Mon, 29 Mar 2021 20:28:16 GMT

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

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

Anonymous — Mon, 29 Mar 2021 20:35:51 GMT

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.

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

lbendlin — Mon, 29 Mar 2021 20:53:42 GMT

Do you need the month name or the month number?

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

Anonymous — Mon, 29 Mar 2021 20:56:20 GMT

I would need month and year.

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

lbendlin — Mon, 29 Mar 2021 20:57:50 GMT

Please revise your desired outcome example.

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

Anonymous — Mon, 29 Mar 2021 21:50:28 GMT

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

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

lbendlin — Mon, 29 Mar 2021 22:08:40 GMT

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?

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

Anonymous — Mon, 29 Mar 2021 22:18:02 GMT

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.

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

lbendlin — Mon, 29 Mar 2021 23:37:40 GMT

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])

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

Anonymous — Mon, 29 Mar 2021 23:37:31 GMT

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)

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

lbendlin — Mon, 29 Mar 2021 23:49:37 GMT

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])

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

Anonymous — Tue, 30 Mar 2021 20:40:22 GMT

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"

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

lbendlin — Tue, 30 Mar 2021 21:05:49 GMT

Please show the DAX code where you hit that issue.

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

Anonymous — Tue, 30 Mar 2021 21:08:24 GMT

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

lbendlin — Tue, 30 Mar 2021 21:11:47 GMT

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.

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

Anonymous — Tue, 30 Mar 2021 21:18:01 GMT

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