Cumulative Count

[Anonymous]

Hey,

I have a column called Hired In which gives the number of days back an employee was hired in. Hired in = DATEDIFF(Hire[Hire Date], Today(),Day)

I have grouped them as follows : Last 30 Days, Last 60 Days and Last 90 Days. The formula I used is as follows:

Hired in buckets = SWITCH(TRUE(), Hires[Hired in]<0, "Others", Hires[Hired in]>=0 && Hires[Hired in]<=30, "Last 30 days", Hires[Hired in]>=0 && Hires[Hired in]<=60, "Last 60 days", Hires[Hired in]>=0 && Hires[Hired in]<=90, "Last 90 days")

Basically I want the count of hires in Last 60 days = Combination of 0 - 60 Days and similarly Last 90 Days to give the total count so far ( Hence >=0 && <=90).  But when I make a visual of it, the values do not show that way.  In the following Image it only considers days 31 -  59 under it and not from 0. Hence, it does not give the cumulative count.

Any suggestions to make this work? Thank you!

[edhans]

I don't think you can use SWITCH like that as you want someone hired 1 day ago to appear in the 30, 60 and 90 day bucket. SWITCH puts it in one bucket only, not multiple.

Try multiple measures. For example:

Hired Last 30 Days = 
CALCULATE(
    COUNT('Hire Dates'[Hired Days Ago]),
    FILTER('Hire Dates','Hire Dates'[Hired Days Ago] <= 30)
)

Hired Last 60 Days = 
CALCULATE(
    COUNT('Hire Dates'[Hired Days Ago]),
    FILTER('Hire Dates','Hire Dates'[Hired Days Ago] <= 60)
)

Etc. Drop those measures in a table.

IS that what you are looking for?

[Anonymous]

@edhans This calculation would give the right answer but I would not get an appropriate bar chart hat shows me those values right?

I want the axis to show "LAst 30 Days", "LAst 60 Days", "Last 90 Days" and the values are those which are calcualted as you mentioned

[edhans · Accepted solution]

I just dropped the three measures into a bar chart and got this:

Is that not what you want?

[Anonymous]

@edhans Hey this works! thank you.

[edhans]

Great @Anonymous - glad my idea helped.

[edhans]

If that is not what you want, look at this file and then tell me how you would want it to be and why the 3 measures (30/60/90) aren't working for you correctly. I'll check back in in an hour or so, or someone else could jump in with the solution you are seeking.

[PattemManohar]

As per the image above, you need to show count as 11 for "Last 60 Days" isn't it ?

If you had your grouping done already, then it should be a straight-forward approach.... please make sure that you are changing the aggregation to "Count" instead of "Sum" (which is the default behaviour for Numeric fields)

[Anonymous]

@PattemManohar Hey, the other solution is aligned to what i wanted. Thank you!