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.
I have this column which have dates , these dates show the day on which employees are "present"
How to calculate absent days (other days which are not in this column) ? in powerbi
Solved! Go to Solution.
Hi @usman96,
You can use below calculate column to achieve your requirement:
present = COUNTROWS ( FILTER ( ALL ( list ), YEAR ( [Date] ) = YEAR ( EARLIER ( list[Date] ) ) && MONTH ( [Date] ) = MONTH ( EARLIER ( list[Date] ) ) ) ) absent = DAY ( DATE ( YEAR ( [Date] ), MONTH ( [Date] ) + 1, 1 ) - 1 ) - [present]
Result:
Regards,
Xiaoxin Sheng
Hi @usman96,
You can use below calculate column to achieve your requirement:
present = COUNTROWS ( FILTER ( ALL ( list ), YEAR ( [Date] ) = YEAR ( EARLIER ( list[Date] ) ) && MONTH ( [Date] ) = MONTH ( EARLIER ( list[Date] ) ) ) ) absent = DAY ( DATE ( YEAR ( [Date] ), MONTH ( [Date] ) + 1, 1 ) - 1 ) - [present]
Result:
Regards,
Xiaoxin Sheng
HI @usman96
You could join to a Date table and add two columns. The first column showing a 1 on dates that match your list. The second column shows a 1 on days where there is no match.
Does that sound like it might work for you?
it would be better if could show them in bar dual bar chart
The first bar show the present days in number like ( 7 present days)
and the 2nd chart show the absent days in numbers like ( 2 absent days )
Can you show me how to achieve this ?
HI @usman96
Can you please mock something up (using your sample data) as I'm still not 100% clear.
I'm sure it's possible to come up with a formula, I just don't understand what you are trying to do (Sorry)
"You could join to a Date table and add two columns. The first column showing a 1 on dates that match your list. The second column shows a 1 on days where there is no match."
Can you implement this and give me some sample of what you said ?
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 |
---|---|
112 | |
97 | |
85 | |
67 | |
59 |
User | Count |
---|---|
150 | |
120 | |
100 | |
87 | |
68 |