Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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)
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
111 | |
95 | |
77 | |
68 | |
54 |
User | Count |
---|---|
144 | |
105 | |
102 | |
89 | |
63 |