Keyword Comparison with Previous Months.

[Arvind_123]

Hi Team,

I have table name BT3 Keyword (2) which have colum like Keyword, Month Id(Month Number), Total Revenue.
so I actually want to know from this If any keyword are present in current month and it is not present in previous months then Bucket it as "New".

if any of keyword are present in previous month then Bucket it as "Others". How to do this in Power Bi Dax.

Keyword	December	November	October	September
cellulitis in leg		1.007
menards cyber monday ads for 2023		1.007
5-ingredient broccoli casserole recipes		1.007
5g towers me map location		1.007

In above table you can clear find there is revenue for 5 keywords in November month i.e, Month Id 11 where as there is no revenue for those keyowrd in prior months i.e, in October and Sept i.e, Month Id 10,9.
So We can say these % keyowrds are New for November month i.e, New For Month Id 11 the unique count are 4 in this case.
so I want to achieve this scenario for all the months.

Thanks,

Arvind

Suppose

[Arvind_123]

Dear @amustafa ,
I do not have date column. I have only Month Id or you can say Month Number.

Thanks,

Arvind

[amustafa]

Hi @Arvind_123 

Assuming your base(source) table looks like this...

KeywordDateRevenue

Item A	1/1/2024	100
Item B	1/1/2024	200
Item C	1/1/2024	150
Item A	1/2/2024	100
Item D	1/3/2024	100

You can then create a DAX calulated clolumn as following:

IsNew =

VAR CurrentKeyword = BT3[Keyword]

VAR CurrentDate = BT3[Date]

VAR CurrentMonthYear = FORMAT(CurrentDate, "MM/YYYY")

VAR FirstDateForKeyword =

    CALCULATE(

        MIN(BT3[Date]),

        FILTER(

            BT3,

            BT3[Keyword] = CurrentKeyword

        )

    )

RETURN IF(CurrentDate = FirstDateForKeyword && CurrentMonthYear = FORMAT(CurrentDate, "MM/YYYY"), 1, 0)

Results:

BT3[Keyword] BT3[Date] BT3[Revenue] BT3[IsNew]
Item A Mon Jan 01 2024 00:00:00 GMT+0500 (Pakistan Standard Time) 100 1
Item A Tue Jan 02 2024 00:00:00 GMT+0500 (Pakistan Standard Time) 100 0
Item B Mon Jan 01 2024 00:00:00 GMT+0500 (Pakistan Standard Time) 200 1
Item C Mon Jan 01 2024 00:00:00 GMT+0500 (Pakistan Standard Time) 150 1
Item D Wed Jan 03 2024 00:00:00 GMT+0500 (Pakistan Standard Time) 100 1

If I answered your question, please mark this thread as accepted.
Follow me on LinkedIn:
https://www.linkedin.com/in/mustafa-ali-70133451/

[Arvind_123]

Hi ,

I have used this dax query to find out the New count in power bi desktop:

step 1: I have created a calculated column as t find New vs Common:

KeywordStatus = IF ( NOT ( COUNTROWS ( FILTER ( ALL('BT3 Keyword (2)'), 'BT3 Keyword (2)'[Keyword] = EARLIER('BT3 Keyword (2)'[Keyword]) && 'BT3 Keyword (2)'[Month Id] < EARLIER('BT3 Keyword (2)'[Month Id]) ) ) ), "New", "Common" ) ​

step 2:
I have created a measure to find out Distictcountof New keyword as:
NewC = CALCULATE( DISTINCTCOUNT('BT3 Keyword (2)'[Keyword]), FILTER( 'BT3 Keyword (2)', 'BT3 Keyword (2)'[KeywordStatus] = "New" ) ) 

I got the exact distinct count as I have worked on excel in overall scenario by using this steps,
but when I make table with management group and NewC then it is showing lesser value.

Is this because of I have different different management group

and suppose I have keyword that is present in all managemnet group so this dax are using

distictcount of one for all management group or any specific management group.

I want to calculate all keyword for every management group if it is showing for one management group

then also count it for that or if it is showing for all management group then count in all managment group.

Thanks,

Arvind

[v-shex-msft]

Hi @Arvind_123 ,

Can you please share a pbix or some dummy data that keep the raw data structure with expected results? It should help us clarify your scenario and test to coding formula.

How to Get Your Question Answered Quickly  

Regards,

Xiaoxin Sheng