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 w...
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

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?