cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
cristianPW
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.

 Report.JPG

 

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)

Report chrono.JPG

 

  • 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:

 

Relationships.JPG

 

Thanks in advance for any suggestion

1 ACCEPTED SOLUTION

Hi @cristianPW ,

what about the following calculation?

 

08-05-_2021_00-14-58.png

 

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)

View solution in original post

4 REPLIES 4
cristianPW
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

FrankAT
Super User II
Super User II

Hi @cristianPW ,

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

 

07-05-_2021_20-28-30.png

 

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)

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?

Hi @cristianPW ,

what about the following calculation?

 

08-05-_2021_00-14-58.png

 

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)

View solution in original post

Helpful resources

Announcements
PBI User Groups

Welcome to the User Group Public Preview

Check out new user group experience and if you are a leader please create your group!

MBAS on Demand

Microsoft Business Applications Summit sessions

On-demand access to all the great content presented by the product teams and community members! #MSBizAppsSummit #CommunityRocks

Get Ready for Power BI Dev Camp

Power BI Dev Camp - June 24th

Mark your calendars and join us for our next Power BI Dev Camp!

Top Solution Authors
Top Kudoed Authors