Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
deanbland
Helper III
Helper III

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

Hi, 

 

I have a dataset similar to this: 

 

Export DateRisk IDTower
06/06/2021RID1Tower A
06/06/2021RID2Tower A
07/07/2021RID1Tower A
07/07/2021RID2Tower A
07/07/2021RID3Tower A
08/08/2021RID1Tower A
08/08/2021RID2Tower A
08/08/2021RID3Tower A
08/08/2021RID4Tower 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?

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

View solution in original post

7 REPLIES 7
CNENFRNL
Community Champion
Community Champion

Screenshot 2021-08-05 213902.png


Thanks to the great efforts by MS engineers to simplify syntax of DAX! Most beginners are SUCCESSFULLY MISLED to think that they could easily master DAX; but it turns out that the intricacy of the most frequently used RANKX() is still way beyond their comprehension!

DAX is simple, but NOT EASY!

sevenhills
Super User
Super User

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

 

sevenhills
Super User
Super User

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

 

sevenhills_0-1628182786997.png

 

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]
            )
        )
    )

 

 

 

sevenhills_2-1628182882997.png

 

sevenhills_1-1628182868582.png

 

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/

@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 🙂 

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

This works perfectly, thank you!!

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! 

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.