Get the not reported weeks

[sonalisaha2310]

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]

Hi @sonalisaha2310 - 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!!

[sonalisaha2310]

Hello @rajendraongole1 , i am getting an error as 
except doesn't support joining acolumn of type text with another column of type number

[v-yiruan-msft · Accepted solution]

@rajendraongole1 Thanks for your contribution on this thread.

Hi @sonalisaha2310 ,

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