Forum Discussion
Cumulative Count
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!
I just dropped the three measures into a bar chart and got this:
Is that not what you want?
8 Replies
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)
PattemManohar Hey, the other solution is aligned to what i wanted. Thank you!
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?
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
I just dropped the three measures into a bar chart and got this:
Is that not what you want?
