cancel
Showing results for
Did you mean:
Regular Visitor

## Solved: Cumulative COUNTROWS time sorting

Hello,

I've built this simple chart to compare over time (monthly) the cumulative number of candidates, offers and hires.

It's worth to mention that for those 3 tables I have their respective measures to calculate the cumulative number of rows.
Below the Candidates one for instance:

``````Cumulative Monthly Candidates =
IF(MAX('Calendar'[Month]) <= MONTH(TODAY()),
CALCULATE([Total Candidates],
FILTER(
ALLSELECTED('Calendar'),
'Calendar'[Month] <= MAX('Calendar'[Month])
)
), BLANK()
)``````

Now I wanted to refine it a little bit but I'm struggling with some details:

• the table visual on the right side show Month field (which is actually the Month Name in the Calendar table), but sorting is alphabetical instead of chronological. How do I change the sorting there?
• the chronological sorting seems to be wrong for the line chart too, because if I add the Year field, the Offer I have in Jan 2022 is shown before those in 2021. Ho do I manage a coherent timeline for these records? (see below)

• the Hires line starts from April because there are no hires for that month but I would like the line to drop to zero when there are no records for that specific month. How do I make the measure able to set 0 instead of the empty value?

Here are the relationships:

Thanks in advance for any suggestion

1 ACCEPTED SOLUTION
Super User II

Hi @cristianPW ,

``````Count of Candidates = COUNT(Candidates[Created On])

Running Total =
IF (
[Count of Candidates] = 0,
BLANK (),
CALCULATE (
[Count of Candidates],
FILTER (
ALL ( 'Calendar'[Date] ),
'Calendar'[Date] <= MAX ( 'Calendar'[Date] )
)
)
)``````

With kind regards from the town where the legend of the 'Pied Piper of Hamelin' is at home
FrankAT (Proud to be a Datanaut)

4 REPLIES 4
Regular Visitor

Thanks again @FrankAT the solution does exactly what I was looking for. It also helped me to better understand how to approach such calculations

Super User II

Hi @cristianPW ,

the only thing you have to do is to sort month name by month (number) (see figure):

If your visuals doesn't refresh you must build them from scratch again. Your corrected PBIX-File is attached.

With kind regards from the town where the legend of the 'Pied Piper of Hamelin' is at home
FrankAT (Proud to be a Datanaut)

Regular Visitor

Thank you very much @FrankAT that solved the first thing, which was the sorting.

But if I put the Year in, then calculations are wrong. I think there's something wrong in my measure, isn't it?

Super User II

Hi @cristianPW ,

``````Count of Candidates = COUNT(Candidates[Created On])

Running Total =
IF (
[Count of Candidates] = 0,
BLANK (),
CALCULATE (
[Count of Candidates],
FILTER (
ALL ( 'Calendar'[Date] ),
'Calendar'[Date] <= MAX ( 'Calendar'[Date] )
)
)
)``````

With kind regards from the town where the legend of the 'Pied Piper of Hamelin' is at home
FrankAT (Proud to be a Datanaut)

Announcements