Get count of ID in the last 7 days , counting duplicate dates as one

[Pandadev]

I am struggling to get a formula to work , where I have a list of ID's and dates . and I want a count ID based the last 7 days

I have added this column to get the 7 day date -     7 days = Today()-7

I then added another column with this formula

Count ID 7 days = (CALCULATE(COUNTA('TU Feed'[ID]),FILTER(ALLSELECTED('TU Feed'),'TU Feed'[ID]=EARLIER('TU Feed'[ID])&&'TU Feed'[departure_date]>='TU Feed'[7 Days])))

But it is not working correctly. I have tried researching this for hours but can't get it to work. any help would be much appreciated

An example of my data in TU Feed

ID  - departure_date  -   time

1   - 20/04/2020        - 01:20

2   - 20/04/2020        -04:50

3  -  20/04/2020        -05:45

1  - 21/04/2020        - 06:45

1  - 21/04/2020        - 12:45  [exclude as duplicate date]

2  - 21/04/2020         - 04:20

2  - 21/04/2020         - 09:20 [exclude as duplicate date]

1  - 22/04/2020         - 10:45

1  - 22/04/2020         - 19:45

2  - 22/04/2020         - 04:20

3  - 22/04/2020         - 09:20

ID 1 - should equal 3

ID 2 - should equal 3

ID 3 - should equal 2

[v-alq-msft · Accepted solution]

Hi, @Pandadev 

Based on your description, today is 4/28/2020.  I created data to reproduce your scenario.

Table:

You may create a measure as below.

Count = 
var _id = SELECTEDVALUE('Table'[ID])
return
CALCULATE(
    DISTINCTCOUNT('Table'[Departure_date]),
    FILTER(
        ALLSELECTED('Table'),
        'Table'[ID] = _id&&
        'Table'[Departure_date]>=TODAY()-7&&
        'Table'[Departure_date]<=TODAY()
    )
)

Result:

Best Regards

Allan

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Pandadev]

Thanks , for your help , i changed ALLSELECTED to just ALL in your formula , and it now works perfectly.

I think as I was filtering the table to show latest date , the  ALL SELECTED method was not working. 

[jthomson]

Why wouldn't a distinctcount of the date field work?

[Pandadev]

In my visual IU am filtering by latest date , so it only shows the ltest departure_time per ID , so when I do that it only shows 1 , as I assume it is filtering out all of the previous departure dates. i wanted to be able to show just the latest departure and then show how many in the past 7 days . 

[kentyler]

The simplest form of the answer is: you have to use ALL() to restore the rows of the table so you can count them when the visuals are just displaying one row.

I wrote:

A measure to get the start date: 

CurrentDay = Today()

A measure to get the date 7 days back:

SevenDaysAgo = [CurrentDay]-7

And then a measure to count the distinct number of flights between the start date and 7 days back:

Seven Day Count =

var acount = calculate(DISTINCTCOUNT('Table'[Date]),
                                               filter(ALL('Table'),'Table'[Date]<=[SevenDaysAgo]))

the catch is that calculate will not accept the date comparison directly, so I had to wrap it in FILTER()

the ALL() as an argument to FILTER() makes sure I'm filtering all the records in 'Table', regardless of any filters being imposed by visuals on the report (which might be showing only one row)

return acount