Forum Discussion

Yggdrasill's avatar
Yggdrasill
Responsive Resident
7 years ago
Solved

Count based on multiple criteria

I have this demotable. ColumnB has many different values, as well as Column C. Disregard ColumnA ColumnA Date ColumnB ColumnC Calculate count 07 21.9.2017 1000 A 1 07 22.9.2017 10...
Need Help
  • LivioLanzo's avatar
    LivioLanzo
    7 years ago

    Hi Yggdrasill

     

    you can do thi sin 2 steps:

     

    first add this column:

     

     

    AddedCol1 = 
ISEMPTY(
    CALCULATETABLE(
        Data,
        ALLEXCEPT( Data, Data[ColumnB], Data[ColumnC] ),
        Data[Date] = EARLIER( Data[Date] ) - 1 
    )
) + 0

     

    and then this final column:

     

     

    AddedCol2 = 
IF(
    Data[AddedCol1] = 0,
    BLANK(),
    CALCULATE(
        SUM( Data[AddedCol1] ),
        ALLEXCEPT( Data, Data[ColumnC], Data[ColumnB] ),
        Data[Date] <= EARLIER( Data[Date] )
    )
)

     

     

     

LivioLanzo's avatar
LivioLanzo
Solution Sage
7 years ago

Yggdrasill 

 

I think you just need to change the 'AddCol1' formula to:

 

AddedCol1 = 
var subDays = IF( Data[Day of Week] = 2, 3, 1 )
return
ISEMPTY(
    CALCULATETABLE(
        Data,
        ALLEXCEPT( Data, Data[ColumnB], Data[ColumnC] ),
        Data[Date] =  EARLIER( Data[Date] ) - subDays
    )
) + 0
Yggdrasill's avatar
Yggdrasill
Responsive Resident
7 years ago

That could work well in case of mondays. However, I'm dealing with holidays too !

I have a Holiday table merged to my datetable so all holiday rows are marked with 0 and 1 if their active. I basically need to count the days between the last active day and current active day and use that variable for the subDays

  • LivioLanzo's avatar
    LivioLanzo
    Solution Sage
    7 years ago

    Hi Yggdrasill 

     

    are you able to post some sample data with your date table included?