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 a...
Need Help
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

 

 

 

 

  • Anonymous's avatar
    Anonymous
    Not applicable
    2 years ago

    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