Count ytd with multiple date columns

[Anonymous]

Hi,

I'm trying to count the number of assignments that has been done ytd and mtd.

Also, I'm counting assignment that has ended.

I have this table Assignments:Assignments

I created a date table:

Dates

I've connected dates to startdate and enddate:

connections

So, As I said. trying to count the number of assignments, and have been several ways to do it. I want it to be generic, in the way that it updates itself when it's new year etc.

I have counted total assigments:

TotalAssignments = COUNT(Assignments[AssignmentId])

gives me 3359

So ytd, this formula:

Candidates_ytd_datesytd = TOTALYTD(count(Assignments[AssignmentId]); Assignments[StartDate]; all(Assignments[StartDate]))

Gives me the result 2

Also:

Candidates_ytd_datesytd = 
     CALCULATE([TotalAssignments];
        DATESYTD('Dates'[Date].[Date]))

Gives me blank

Either way I do it, I get blank or 2. I've checked the data, and it should be 400something.

Can anyone point me in the right direction?

[Anonymous]

Look closely at the [StartDate] and [EndDate] values in your Assignments table.  They are DATETIME, and not midnight values.  The Start time shown is 6:00 AM.  That will NOT match your calendar table for the same day at 12:00 AM.

Change the type on the [StartDate] and [EndDate] columns from DateTime to date, and you should be good.

Also, your model size should shrink a LOT.  You'll have a much lower cardinality in these 2 columns, because the # of unique values will be much lower.

If you truly need the time portion of the [StartDate] and [EndDate] columns, then split them out into [StartTime] and [EndTime]...adding 2 more columns to your model.  The resulting model should still be smaller, since the overall compression should be better.

[Anonymous]

Hi @Anonymous .

Thanks a lot! I'm getting better results now.

But is it a way to use StartDate and EndDate that I have in my Assignment table now?

Assignments[EndDate].[Date] does not work anymore after I made the connection to the Dates

E.g: when I'm going to find candidates for the current month, this gives me 0 results

Candidates_starts_datesmtd = 
     CALCULATE([TotalYTD];
        DATESMTD(Dates[Date].[Date]))

And this gives me 4 (which is correct)

Candidates_starts_datesmtd = 
     CALCULATE([TotalYTD];
        DATESMTD(Assignments[StartDate]))

Since I'm unable to see the dates I get returned I get confued why one works and the other one don't.

[Anonymous]

I'm still having issues.

It really doesn't count properly. I've doble checked that the data is there, but it's not giving me the result's I want. No mater if I use dates.date or startdate.date

Like this:

Measure = CALCULATE(COUNT(Assignments[AssignmentId]); NEXTQUARTER(Assignments[StartDate].[Date]))

Gives me blank. But it should have returned 0. Same if I use [StartDate].[Date]