Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
5 years ago
Solved

Calculated Column based on multiple filter criteria

Hello, How to create a calculated column flagging whether or not a particular calendar Week is shared between two calendar months or not (end of month scenario), without considering Saturdays and Su...
Need Help
  • amitchandak's avatar
    amitchandak
    5 years ago

    Anonymous ,


    Var _week = [Week]
    return
    if(calculate(distinctCOUNT(Dim_Date[Month Number]), filter(Dim_Date, Dim_Date[Week] =_week && not( Dim_Date[Weekday] in{"Sun", "Sat"}))) =2, "Yes", "No")

Anonymous's avatar
Anonymous
Not applicable
5 years ago

Hi amitchandak 

Thanks much for quick help. Really appreciate. It works exactly like required.

I used the FILTER syntax you provided and sourrounded it with ALLEXCEPT- this works
But ALLEXCEPT sourrounded by FILTER - this does not work 

Would be very helpful if you could explain a bit on this. Thanks in advance.


This works:

 

Week shared between months = 
IF(
       CALCULATE(
           CALCULATE
                    ( 
                    DISTINCTCOUNT(Dim_Date[Month Number]), 
                    FILTER(Dim_Date, NOT (Dim_Date[Weekday] IN {"Sun", "Sat"}))
                     ),
                    ALLEXCEPT(Dim_Date,Dim_Date[Week])
                )
 = 2, "Yes", "No"
 )

 

This does NOT work. (Result of IF is 12 always)

 

IF(
       CALCULATE(
           CALCULATE
                    ( 
                    DISTINCTCOUNT(Dim_Date[Month Number]), 
                    ALLEXCEPT(Dim_Date,Dim_Date[Week])
                     ),
                   FILTER(Dim_Date, NOT (Dim_Date[Weekday] IN {"Sun", "Sat"}))
                )
 = 2, "Yes", "No"
 )

 

Thanks

Nirmit