Forum Discussion
Get the not reported weeks
Hello I have two files
One file contains the list of all the weeks
| weeks |
| 01/01/2024 - 01/07/2024 |
| 01/08/2024 - 01/15/2024 |
| 01/16/2024 - 01/23/2024 |
| 01/24/2024 - 01/31/2024 |
and the another file contain the time entried
| Name | ID | Week | Sun | Mon | Tue | Wed | Thur | Fri | Sat |
| Sonali | 1111 | 01/01/2024 - 01/07/2024 | 1 | 3 | 4 | 5 | 2 | 4 | 0 |
| Sonali | 1111 | 01/08/2024 - 01/15/2024 | 9 | 3 | 2 | 5 | 1 | 2 | 6 |
the need is to get the weeks of an user which is not reported in the list as below
| name | id | date | sun | mon | tue | wed | thur | fri | sat |
| sonali | 1111 | 01/16/2024 - 01/23/2024 | |||||||
| sonali | 1111 | 01/24/2024 - 01/31/2024 |
rajendraongole1 Thanks for your contribution on this thread.
Hi Anonymous ,
I created a sample pbix file(see the attachment), please check if that is what you want.
1. Unpivot these weekday columns in Power Query Editor
= Table.Unpivot(#"Changed Type", {"Sun", "Mon", "Tue", "Wed", "Thur", "Fri", "Sat"}, "Weekday", "Value")2. Create a measure as below
Measure = CALCULATE(SUM('Data'[Value]),FILTER('Data','Data'[Week] =SELECTEDVALUE('Weeks'[weeks])))3. Create visuals
Best Regards
3 Replies
Hi Anonymous - create a calculated table as below
FilteredMissingWeeks =EXCEPT (MissingWeeks,SELECTCOLUMNS ('Time Entry',"Name", 'Time Entry'[Name],"ID", 'Time Entry'[ID],"Week", 'Time Entry'[Week],"Sun", 'Time Entry'[Sun],"Mon", 'Time Entry'[Mon],"Tue", 'Time Entry'[Tue],"Wed", 'Time Entry'[Wed],"Thu", 'Time entry'[Thur],"Fri", 'Time Entry'[Fri],"Sat", 'Time Entry'[Sat]))Hope it works
Did I answer your question? Mark my post as a solution! This will help others on the forum!
Appreciate your Kudos!!Hello rajendraongole1 , i am getting an error as
except doesn't support joining acolumn of type text with another column of type numberrajendraongole1 Thanks for your contribution on this thread.
Hi Anonymous ,
I created a sample pbix file(see the attachment), please check if that is what you want.
1. Unpivot these weekday columns in Power Query Editor
= Table.Unpivot(#"Changed Type", {"Sun", "Mon", "Tue", "Wed", "Thur", "Fri", "Sat"}, "Weekday", "Value")2. Create a measure as below
Measure = CALCULATE(SUM('Data'[Value]),FILTER('Data','Data'[Week] =SELECTEDVALUE('Weeks'[weeks])))3. Create visuals
Best Regards
