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

v-luwang-msft's avatar
v-luwang-msft
Community 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