Forum Discussion

Arvind_123's avatar
Arvind_123
Icon for Helper I rankHelper I
2 years ago

Keyword Comparison with Previous Months.

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.

 

KeywordDecemberNovemberOctoberSeptember
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

Need Help

4 Replies

  • amustafa's avatar
    amustafa
    Icon for Solution Sage rankSolution Sage
    2 years ago

    Hi Arvind_123 

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

    KeywordDateRevenue

    Item A1/1/2024100
    Item B1/1/2024200
    Item C1/1/2024150
    Item A1/2/2024100
    Item D1/3/2024100

     

    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's avatar
      Arvind_123
      Icon for Helper I rankHelper I
      2 years ago

      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

       

       

       

       

  • Arvind_123's avatar
    Arvind_123
    Icon for Helper I rankHelper I
    2 years ago

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

     

    Thanks,

    Arvind