Count Jobs within Dates Selected

[Matt_Mohawk]

Hi,

I have a list of jobs that have entered dates. I have a date table connected to it as well. The date table is connected to the add-in called ChicletSlicer. When I select the year and the month, for January 2022, it says 62 distinct jobs. If I select February, it says 64 distinct jobs. If you add that together you get 126 total jobs. When I do that right now I get 108 jobs because I am using distinct jobs. I would like a formula that does not count jobs that have been entered in the past. Right now it isn't detecting that job number 123456 was in January so really it is counting it twice. See screenshots below for more information.

Let me know if you need anymore information.

[Ashish_Mathur]

Very confused.  What do you want the answe to be?  If you want the answer to be 126, then write this measure

Jobs = distinctcount(Data[Job number])

Total = SUMX(VALUES(Calendar[Month name]),[Jobs])

Ensure that you slicers for Year and Month name are from the Calendar Table.

[Matt_Mohawk]

Sorry about the confusion. I don't want the total to be 126. I need it to look at my data that runs from 2019 to current data and check to see if the job number has been used before. If so, then it can't be a new job unless that job was started within the given timeframe. For example, if there is a job that was entered in 2020 and I am searching for any new jobs in 2022, the job in 2020 should not show up on the list as a new job.

[Ashish_Mathur]

Hi,

Ensure that you have a Calendar Table with calculated column formulas for year, Month name and Month number.  Sort the Month name column by the Month number.  Create a relationship (Many to One and Single) from the Date column of the Data Table to the Date column of the Calendar Table.  to your visual, drag year and Month name from the Calendar Table.  Write these measures

Date fo first interaction = calculate(min(Data[Transaction date]),datesbetween(calendar[date],minx(all(calendar),calendar[Date]),max(calendar[date])))

New customers = countrows(filter(values(data[customer code]),[Date fo first interaction]>=min(calendar[date])))

Drag the second measure to a card visual.

Hope this helps.

[Matt_Mohawk]

I appreciate the formula that you provided but for some reason it doesn't work correctly. Can you include what my calendar formula should look like? This is what it looks like on my side.

Date =

ADDCOLUMNS (

CALENDAR (DATE(2017,1,1), DATE(2032,12,31)),

"DateAsInteger", FORMAT ( [Date], "MMDDYY" ),

"Year",YEAR([Date]),

"Monthnumber", FORMAT ( [Date], "MM" ),

"YearMonthnumber", FORMAT ( [Date], "YYYY/MM" ),

"YearMonthShort", FORMAT ( [Date], "YYYY/mmm" ),

"MonthNameShort", FORMAT ( [Date], "mmm" ),

"MonthNameLong", FORMAT ( [Date], "mmmm" ),

"DayOfWeekNumber", WEEKDAY ( [Date] ),

"DayOfWeek", FORMAT ( [Date], "dddd" ),

"DayOfWeekShort", FORMAT ( [Date], "ddd" ),

"Quarter", "Q" & FORMAT ( [Date], "Q" ),

"YearQuarter", FORMAT ( [Date], "YYYY" ) & "/Q" & FORMAT ( [Date], "Q" )

)

[Ashish_Mathur]

Hi,

Create the Calendar table with the following formula

Calendar = calendar(DATE(2017,1,1), DATE(2032,12,31))

Write these calculated column formulas

Year = year(Calendar[Date])

Month name = format(calendar[Date],"mmmm")

Month number = month(calendar[Date])

Sort the Month name column by the Month number.  to your visual, drag Year and Month name from the Calendar Table.

Hope this helps.

[Matt_Mohawk]

I think I follow what you said. I will give that a try and let you know if it works.