Power BI to find the first missing week

[KumarPatel80]

Hi, As a novice user, im struggling to write a query (DAX, Power Query or M query) to accomplish something here. I have three tables: Users, Calendar and Users By Week:

Based on the UsersByWeek and Calendar tables,  Blue and Yellow users have missing weeks:

 

I require a new column added to USERS table to store the first missing Week #(Calendar.Index) per user.  If there is no missing week(as is Green and Red) then the first Week # is 0(zero).  This is the desired result:

Can anyone help with achieving this please?

[Anonymous]

Hi @KumarPatel80 ,

Below is my table1:

Below is my table2:

Below is my table3:

You can create a measure to achieve it:

FirstMissingWeek1 = 
VAR UserWeeks = 
    CALCULATETABLE(
        VALUES('USERSBYWEEK'[WC_Date]),
        'UsersByWeek'[User] = SELECTEDVALUE(USER[User])
    )
VAR AllWeeks = VALUES('CALENDAR'[WC_Date])
VAR MissingWeeks = EXCEPT(AllWeeks, UserWeeks)
VAR FirstMissingWeek = MINX(MissingWeeks, 'CALENDAR'[Measure])
RETURN
    IF(ISBLANK(FirstMissingWeek), 0, FirstMissingWeek)
Measure = 
     SELECTEDVALUE('CALENDAR'[Index])

The final output will be like this:

Best Regards,

Xianda Tang

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[KumarPatel80]

Hi v-xiandat-msft,

That solution is perfect except is there is anyway to contain those in 

'FirstMissingWeek1' values in the USERS table?  The reason I ask is becuase I would like to introduce a slicer to to filter records by missing weeks greater than 2 for instance?  Thanks

[amitchandak]

@KumarPatel80 , Try measures like

M1 = countrows(userbyweek)

M2= Sumx(Values(User[user]), calculate( countx(Values(calendar[Week Year]), if(isblank([M1]), [Week Year], blank())) ))

very similar to

Power BI Items/Categories not sold to the customers: https://youtu.be/AbuKvAnicwo

[KumarPatel80]

Thanks for your reply.  

[Anonymous · Accepted solution]

Hi @KumarPatel80 ,

IF you want to filter greater than 2 weeks ,  you can apply this measure to visual filter:

Because measure can not applt to slicer , this method can achieve your idea.

Best Regards,

Xianda Tang

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[KumarPatel80]

Thanks for the response.  Thats why I was hoping for the value to be in a column in the Users table.  Is this achievable so that I can use a Slicer visual?