Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
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:
Here are the relationships:
Thanks in advance for any suggestion
Solved! Go to Solution.
Hi @cristianPW ,
what about the following calculation?
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)
Thanks again @FrankAT the solution does exactly what I was looking for. It also helped me to better understand how to approach such calculations
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)
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?
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)
User | Count |
---|---|
140 | |
113 | |
104 | |
77 | |
65 |
User | Count |
---|---|
136 | |
118 | |
101 | |
71 | |
61 |