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

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
Frequent 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
Community Champion
Community Champion

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?

FrankAT
Community Champion
Community Champion

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
Power BI December 2021 Update_carousel 768x460.jpg

Check it Out!

Click here to read more about the December 2021 Updates!

User Group Leader Meeting January 768x460.png

Calling all User Group Leaders!

Don't miss the User Group Leader meetings on January, 24th & 25th, 2022.

Jan 2022 Dev Camp 768x460 copy.png

Power BI Dev Camp- January 27th, 2022

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

Top Solution Authors
Top Kudoed Authors