Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

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

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?

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)

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

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