How to create a calculated column that shows how many new items have been added per month / week

[deanbland]

Hi, 

I have a dataset similar to this: 

Export Date	Risk ID	Tower
06/06/2021	RID1	Tower A
06/06/2021	RID2	Tower A
07/07/2021	RID1	Tower A
07/07/2021	RID2	Tower A
07/07/2021	RID3	Tower A
08/08/2021	RID1	Tower A
08/08/2021	RID2	Tower A
08/08/2021	RID3	Tower A
08/08/2021	RID4	Tower A

I am wanting to create a calculatedcolumn that lets me know if a new Risk ID has been added per month. So the output should be 

June = 0 

July = 1

August = 1

Is there a way I can do this by month and another calculated column that does this by week?

[CNENFRNL]

[sevenhills]

For week, you can use the same measure, all you need to use is the Week from Date table. 

[sevenhills]

I guess you are looking "New Customers" pattern. (and not returning customers pattern)

See if this will work for you:

For testing, I created as

Date table as (you may be having the table)

Date = 

var _cal = CALENDAR(MIN(Table1[Export Date]), Max(Table1[Export Date]))
RETURN ADDCOLUMNS(
    _cal, 
    "Year", YEAR( [Date]),
    "Month Number", MONTH( [Date]),
    "Month", FORMAT([Date], "MMMM")
)

Added MEASURE New Items

NewItems = 
    COUNTROWS (
        FILTER (
            CALCULATETABLE (
                ADDCOLUMNS (
                    VALUES ( Table1[Risk ID] ),
                    "DateOfFirstItem", CALCULATE ( MIN ( Table1[Export Date] ) )
                ),
                ALL ( 'Date' )
            ),
            CONTAINS (
                VALUES ( 'Date'[Date] ),
                'Date'[Date],
                [DateOfFirstItem]
            )
        )
    )

Additional reading: 

Below links has details about it

https://radacad.com/customer-retention-in-power-bi-dax-measures

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

https://blog.enterprisedna.co/new-vs-existing-customers-advanced-analytics-w-dax/

https://www.sqlbi.com/articles/computing-new-customers-in-dax/

[deanbland]

@sevenhills Instead of having a measure, is there a way to create a calculated column that assignes a 1 next to the new entries? If so that would be brilliant! 

Thanks again 🙂 

[sevenhills · Accepted solution]

Yes, you can do as calculated column too. 

Earliest New Item Within Tower = 

var _RID = Table1[Risk ID]
var _Twr = Table1[Tower]
var _FirstDate = CALCULATE( Min(Table1[Export Date]), filter(Table1, Table1[Risk ID] = _RID && Table1[Tower] = _Twr))
RETURN IF ( Table1[Export Date] = _FirstDate, 1, 0)

additional reading: I am not sure the end goal of the final model. I recommend to look:

To add ranking calculated column and then use for your needs.

https://community.powerbi.com/t5/Desktop/Summarize-and-Rank-by-multiple-columns-and-rows/m-p/330108

https://community.powerbi.com/t5/Desktop/Rank-from-Min-to-MAX-Calculated-Column/m-p/1793486

If it is me, I will not do as calculated column for min max by category. I will recommend as aggregate table using M Query. Purely depends on your needs and design way of things.
https://www.ehansalytics.com/blog/2020/7/16/return-row-based-on-max-value-from-one-column-when-group...

[deanbland]

This works perfectly, thank you!!

[deanbland]

Hi @sevenhills , thank you for this! 

Is there a way to add a filter into this too? The 'Tower' column has multiple towers in my dataset and so I want to be able to filter by each individual one? 

Thanks again!