cancel
Showing results for
Did you mean:

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Anonymous
Not applicable

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!

1 ACCEPTED SOLUTION
Super User

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

Is that not what you want?

Did my answers help arrive at a solution? Give it a kudos by clicking the Thumbs Up!

DAX is for Analysis. Power Query is for Data Modeling

Proud to be a Super User!

MCSA: BI Reporting
8 REPLIES 8
Super User

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?

Did my answers help arrive at a solution? Give it a kudos by clicking the Thumbs Up!

DAX is for Analysis. Power Query is for Data Modeling

Proud to be a Super User!

MCSA: BI Reporting
Anonymous
Not applicable

@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

Super User

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

Is that not what you want?

Did my answers help arrive at a solution? Give it a kudos by clicking the Thumbs Up!

DAX is for Analysis. Power Query is for Data Modeling

Proud to be a Super User!

MCSA: BI Reporting
Anonymous
Not applicable

@edhans Hey this works! thank you.

Super User

Great @Anonymous - glad my idea helped.

Did my answers help arrive at a solution? Give it a kudos by clicking the Thumbs Up!

DAX is for Analysis. Power Query is for Data Modeling

Proud to be a Super User!

MCSA: BI Reporting
Super User

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.

Did my answers help arrive at a solution? Give it a kudos by clicking the Thumbs Up!

DAX is for Analysis. Power Query is for Data Modeling

Proud to be a Super User!

MCSA: BI Reporting
Community Champion

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)

Proud to be a PBI Community Champion

Anonymous
Not applicable

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

Announcements

Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

Power BI Monthly Update - August 2024

Check out the August 2024 Power BI update to learn about new features.

Fabric Community Update - August 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors
Top Kudoed Authors