Forum Discussion

Lumegu's avatar
Lumegu
Frequent Visitor
4 years ago
Solved

Rolling count based on two criteria

Dear community,

 

I was hoping you could help out with the following question, I have a dataset whicht contains information about persons and dates whenever they have some duty to forfill (marked with X).

 

I would like to calculate the following count column:

- For each person there needs to be a count of X'es for every row, but it needs to be a rolling count which looks 28 days back

 

I was hoping somebody could help me out with this calculation? If anymore info is needed please let me know

 

PersonDate (ddmmyyyy)DutyCount

Sander

1-1-2022X1
Sander2-1-2022 1
Sander3-1-2022X2
Sander4-1-2022X3
Sander5-1-2022 3
Mark1-1-2022X1
Mark2-1-2022X2
Mark3-1-2022 2
Mark4-1-2022 2
Mark5-1-2022 2
Mark6-1-2022 2

 

Need Help
  • v-luwang-msft's avatar
    v-luwang-msft
    4 years ago

    Hi Lumegu ,

    If you want to use custom column ,try the below:

    test3 = 
var qqq=countx(
    FILTER (
        'Table',
        'Table'[Person] = EARLIER ( 'Table'[Person] )
            && 'Table'[Duty] = "X"
            && 'Table'[Date (ddmmyyyy)]
                >= TODAY () - 28
            && 'Table'[Date (ddmmyyyy)] <= TODAY ()
   ),0)  return if(qqq=BLANK(),0,qqq)

    Output:

    And if you want to use measure:

    test = IF(MAX('Table'[Duty])="X"&&MAX('Table'[Date (ddmmyyyy)])>=TODAY()-28 &&MAX('Table'[Date (ddmmyyyy)])<=TODAY(),1,0)
    test2 = SUMX('Table','Table'[test])

    Output:

    Did I answer your question? Mark my post as a solution!


    Best Regards

    Lucien

3 Replies

  • v-luwang-msft's avatar
    v-luwang-msft
    Icon for Community Support rankCommunity Support
    4 years ago

    Hi Lumegu ,

    If you want to use custom column ,try the below:

    test3 = 
var qqq=countx(
    FILTER (
        'Table',
        'Table'[Person] = EARLIER ( 'Table'[Person] )
            && 'Table'[Duty] = "X"
            && 'Table'[Date (ddmmyyyy)]
                >= TODAY () - 28
            && 'Table'[Date (ddmmyyyy)] <= TODAY ()
   ),0)  return if(qqq=BLANK(),0,qqq)

    Output:

    And if you want to use measure:

    test = IF(MAX('Table'[Duty])="X"&&MAX('Table'[Date (ddmmyyyy)])>=TODAY()-28 &&MAX('Table'[Date (ddmmyyyy)])<=TODAY(),1,0)
    test2 = SUMX('Table','Table'[test])

    Output:

    Did I answer your question? Mark my post as a solution!


    Best Regards

    Lucien

  • amitchandak's avatar
    amitchandak
    Icon for Super User rankSuper User
    4 years ago

    Lumegu , try a new column like

     


    new column =
    var _date = [Date] -28
    return
    countx(filter(Table, [Person] = earlier([Person])&& [Date] <= earlier([Date]) && [Date] >= _date),[Person])

    • Lumegu's avatar
      Lumegu
      Frequent Visitor
      4 years ago

      Thanks for the quick reply, however I only want to count if there is a X in column duty. How could this be added to the calculation?