Count new occurences by year

[florentbignier]

Hi,

I have a table with a school and its date of visit.

I would like, on a report with a filter per year, to display an indicator with the number of schools that have not been visited before.

I imagine that we need a measure to make this indicator, but I do not see how to formulate it.

Example of datas :

School1 ; 17/01/2017

School1 ; 18/05/2018

School2 ; 25/11/2018

School3 ; 15/07/2018

Desired result :

If I filter on 2017 : 1 (there is only school1 visited on 2017)

If I filter on 2018 : 2 (there is 3 schools visited on 2018, but school1 was already visited on 2017 so I don't want count it)

Any idea ?

Thanks in advance,

Florent

[v-yulgu-msft · Accepted solution]

Hi @florentbignier,

Please refer to below measures:

check =
CALCULATE (
    COUNT ( Table1[School] ),
    FILTER (
        ALLEXCEPT ( Table1, Table1[School] ),
        Table1[Date].[Year]
            = YEAR ( SELECTEDVALUE ( Table1[Date] ) ) - 1
    )
)

School Visits by year =
CALCULATE (
    DISTINCTCOUNT ( Table1[School] ),
    FILTER ( ALLSELECTED ( Table1 ), [check] <> 1 )
)

 

Best regards,

Yuliana Gu

[florentbignier]

Perfect @v-yulgu-msft !!

Thank you very much 

[Anonymous]

Hi, You can try this pattern by Marco Russo an Alberto Ferrari. Replace customers with schools. Try it

[Anonymous]

https://www.daxpatterns.com/new-and-returning-customers/

[Anonymous]

All you need is COUNT formula. See sample file

https://ufile.io/3pja1

Steps:

1) Add measure "School Visits by Year" to table, to count number of visits

2) Add measure to page as table

3) Drag "Date" to screen and change to Slicer

4) Click on Slicer and then from the "Field" part of the Slicer, right click on "Date" and choose "Date Hierarchy" so you get the year

5) Change Slicer to List format

[florentbignier]

Thanks chirayuw 🙂

Unfortunately your sample file gives me "3" on "2018", but I don't want to count school1 because this school has already been visited on 2017.

Any other idea ?

Florent

[Anonymous]

try this. current year minus previous year. distinct count of school name by year

School Visits by year = 
VAR CYear //Current Year selected in Slicer
=SELECTEDVALUE(Table1[Date].[Year])

VAR PYear //Previous Year = Above minus 1
=SELECTEDVALUE(Table1[Date].[Year])-1

VAR CCOUNT // Do a distinct count of schools based on school name visited in current year
=CALCULATE(DISTINCTCOUNT(Table1[School]),FILTER(ALL(Table1),Table1[Date].[Year]=CYear))

VAR PCOUNT // Do a distinct count of schools based on school name visited in previous year
=CALCULATE(DISTINCTCOUNT(Table1[School]),FILTER(ALL(Table1),Table1[Date].[Year]=PYear))

Return //current year count minus previous year count
CCOUNT-PCOUNT

[florentbignier]

Interesting, but...

If a school isn't visited in 2018, your formula will give me false result

I complete my data sample :

School4 ; 01/07/2017

School1 ; 17/01/2017

School1 ; 18/05/2018

School2 ; 25/11/2018

School3 ; 15/07/2018

In this case I want :

If I filter on 2017 : 2 (school1 and school4)

If I filter on 2018 : 2 (school2 and school3 but not school1 which was already visited on 2017)

Many thanks for youy time !