Forum Discussion
Leave calendar
hi All
my input table
| Name | Leave Date | Leave Type |
| venkat | 10/1/2024 | Leave |
| venkat | 10/5/2024 | Holiday |
| Raj | 10/3/2024 | Leave |
| Rama Rao | 10/14/2024 | Holiday |
| Raji | 11/12/2024 | Leave |
i need output like this
note :
1.weekend has to mark in grey color as show in image
2. holiday and leave has to mark as shown in image
3. total has show as per the image
please share powerbi file
Regards
Yukta
Hi Anonymous ,
Could you please show more details about your requirement because I have a few questions about it:
1. What does the number of column name represent? Is it the day of the month or something else? If it is a date, there are only 28 days in your table, but there are obviously more than 28 days in October in your data.
2. What total is displayed as a value of 1? Is it the number of holidays?
Best regards,
Mengmeng Li
thanks for the repsonse Mengmeng Li
1. What does the number of column name represent? Is it the day of the month or something else? If it is a date, there are only 28 days in your table, but there are obviously more than 28 days in October in your data.
ans : yes you are write these are days of the month. i removed accidently. please consider as days of month
2. What total is displayed as a value of 1? Is it the number of holidays?
ans : need to block weekends and holidays and leave only rest of things please leave. i shown an sample in the image.
note : suppose if a person take leave need show total
note: weekend has block the entire column in grey color
5 Replies
Hi Anonymous ,
Could you please show more details about your requirement because I have a few questions about it:
1. What does the number of column name represent? Is it the day of the month or something else? If it is a date, there are only 28 days in your table, but there are obviously more than 28 days in October in your data.
2. What total is displayed as a value of 1? Is it the number of holidays?
Best regards,
Mengmeng Li
thanks for the repsonse Mengmeng Li
1. What does the number of column name represent? Is it the day of the month or something else? If it is a date, there are only 28 days in your table, but there are obviously more than 28 days in October in your data.
ans : yes you are write these are days of the month. i removed accidently. please consider as days of month
2. What total is displayed as a value of 1? Is it the number of holidays?
ans : need to block weekends and holidays and leave only rest of things please leave. i shown an sample in the image.
note : suppose if a person take leave need show total
note: weekend has block the entire column in grey color
Hi Anonymous ,
Based on your description, your current table structure does not meet the table structure you expect for your matrix. I suggest you create a custom table that contains all the names and all the days of the month. Here is my test for your reference, you can create more content what you want based on this. I also attached pbix file to the reply for your reference.
Custom table1: Contains all days.
Table 2 = GENERATESERIES(1,31)Custom table2: Combian names and days.
Table 3 = CROSSJOIN(ALL('Table'[Name]),ALL('Table 2'[Value]))Custom table2: Contains all months.
Table 4 = SELECTCOLUMNS(GENERATESERIES(1,12),"Month",[Value])The relationship:
The measure for getting leave type.
LeaveType_2 = VAR leavetype = LOOKUPVALUE('Table'[Leave Type],'Table'[Name],MAX('Table 3'[Name]),'Table'[MonthOfYear],SELECTEDVALUE('Table 4'[Month]),'Table'[DayOfMonth],MAX('Table 3'[Value])) VAR everyday = DATE(2024,SELECTEDVALUE('Table 4'[Month]),MAX('Table 3'[Value])) RETURN IF(NOT(ISBLANK(leavetype)),leavetype,IF(WEEKDAY(everyday)=1||WEEKDAY(everyday)=7,"Weekend",""))Set conditional formatting for LeaveType_2 measure.
Best regards,
Mengmeng Li
Hi Anonymous ,
You can create a year table by following the way the month table was created in My file, and add table relationships.
As for the total column, I don't recommend doing this, which would require changing the current table structure and could cause more problems. In fact, there is an easier way for you to choose, you can use the table to show the count of each person's leave and holiday, you can see in my previous pbix file that it shows very well.
Best regards,
Mengmeng Li