Forum Discussion

thaonguyen's avatar
thaonguyen
Frequent Visitor
3 years ago
Solved

Only compare 1 value per week

Hi all, 

This is my data:

NameDayRequired DateAttendance Date
tien.nguyen9/1/2022  
tien.nguyen9/2/2022  
tien.nguyen9/5/20229/5/20229/5/2022
tien.nguyen9/6/20229/6/20229/6/2022
tien.nguyen9/7/2022  
tien.nguyen9/8/2022 9/8/2022
tien.nguyen9/9/2022  
tien.nguyen9/12/2022 9/12/2022
tien.nguyen9/12/20229/12/20229/12/2022
tien.nguyen9/13/2022  
tien.nguyen9/13/20229/13/2022 
tien.nguyen9/14/2022  
tien.nguyen9/15/2022 9/15/2022
tien.nguyen9/16/2022  
tien.nguyen9/19/2022  
tien.nguyen9/19/20229/19/2022 
tien.nguyen9/20/2022  
tien.nguyen9/20/20229/20/2022 
tien.nguyen9/21/2022  
tien.nguyen9/22/2022 9/22/2022
tien.nguyen9/23/2022  
tien.nguyen9/26/2022 9/26/2022
tien.nguyen9/27/20229/27/20229/27/2022
tien.nguyen9/28/2022  
tien.nguyen9/29/2022 9/29/2022
tien.nguyen9/30/2022  

I had a table as this pic. I hope I create a calculated column to return the Type that "If within a week, user can attendance on 1 required date".

As example, 

-Week 2: <from Sep 4 to Sep 10> -->The required date is on Sep 5 & Sep 6, The user joined both days --> return "Comply"

-Week 3: <from Sep 11 to Sep 17> --> The required date is on Sep 12 & Sep 13, The user only joined Sep 12 -->return "Comply"

-Week 4: <from Sep 18 to Sep 24> -->The required date is on Sep 19 & Sep 20, The user didn't join on both days --> return Violate.

Please give a help, thank you so much.

  • Anonymous's avatar
    Anonymous
    3 years ago

    Hi thaonguyen 

    Please create a calculated column like this :

     

     

    Column = 
 var _count=CALCULATE(COUNT('Table'[Attendance Date]),FILTER('Table',WEEKNUM([Day],2)=WEEKNUM(EARLIER('Table'[Day]),2)&& WEEKNUM([Attendance Date],2)=WEEKNUM([Required Date],2)&&[Attendance Date] in VALUES('Table'[Required Date])))
 return IF([Required Date]<>BLANK(),IF(_count=2,"Comply1",IF(_count=1,"Comply2",IF(_count=BLANK(),"Violate"))))

     

     

    The result is shown as below .

     

    Best Regards,
    Community Support Team _ Ailsa Tao
    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

     

1 Reply

  • Anonymous's avatar
    Anonymous
    Not applicable
    3 years ago

    Hi thaonguyen 

    Please create a calculated column like this :

     

     

    Column = 
 var _count=CALCULATE(COUNT('Table'[Attendance Date]),FILTER('Table',WEEKNUM([Day],2)=WEEKNUM(EARLIER('Table'[Day]),2)&& WEEKNUM([Attendance Date],2)=WEEKNUM([Required Date],2)&&[Attendance Date] in VALUES('Table'[Required Date])))
 return IF([Required Date]<>BLANK(),IF(_count=2,"Comply1",IF(_count=1,"Comply2",IF(_count=BLANK(),"Violate"))))

     

     

    The result is shown as below .

     

    Best Regards,
    Community Support Team _ Ailsa Tao
    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.