Microsoft Fabric Community Conference 2025, March 31 - April 2, Las Vegas, Nevada. Use code MSCUST for a $150 discount.
Register nowThe Power BI DataViz World Championships are on! With four chances to enter, you could win a spot in the LIVE Grand Finale in Las Vegas. Show off your skills.
Dear all,
I'm new to PowerBI DAX language.
I have a combined each month customer statuses and would like to compare their changes month over month. Customer is unique in each month
essentially, I want to
1) lookup last month, profile status for the same customer and know the customer profile and warranty date
2) if customer is missing, it's off boarded and I need to flag it out
I am able to do this kind of lookup using Calculate (sum (last_data), DATEADD( date , -1, month)
but it need aggregation. I want this to be done in account level so I can count how many went up and down, warranty extended and slice/ fitler them when necessary.
hope you all can guide me. thank you in advance
Month1 | Data_date | Account | Profile | Last_Mth_Profile | Changes | Warranty_date | Last_Mth_W_D | Changes |
Jan | 1-Jan | Mr. A | 1 | 1-Jan | ||||
Feb | 1-Feb | Mr. A | 3 | 1 | Up | 31-Dec | 1-Jan | Extended |
Mar | 1-Mar | Mr. A | 3 | 3 | 31-Dec | 31-Dec | ||
Jan | 1-Jan | Mr. B | 3 | 31-Dec | ||||
Feb | 1-Feb | Mr. B | 1 | 3 | Down | 1-Jan | 1-Jan | Cancelled |
Mar | 1-Mar | Off boarded | Off boarded |
Solved! Go to Solution.
Hi @shower999,
Please refer to below DAX formulas to add calculated columns:
Index = RANKX ( FILTER ( Sample1, Sample1[Account] = EARLIER ( Sample1[Account] ) ), Sample1[Data_date], , ASC, DENSE ) Last_Mth_Profile = CALCULATE ( SELECTEDVALUE ( Sample1[Profile] ), FILTER ( ALLEXCEPT ( Sample1, Sample1[Account] ), Sample1[Index] = EARLIER ( Sample1[Index] ) - 1 ) ) Profile_Changes = IF ( Sample1[Account] = BLANK (), "Off boarded", IF ( Sample1[Last_Mth_Profile] = BLANK () || Sample1[Last_Mth_Profile] = Sample1[Profile], BLANK (), IF ( Sample1[Last_Mth_Profile] < Sample1[Profile], "Up", "Down" ) ) ) Last_Mth_W_D = CALCULATE ( SELECTEDVALUE ( Sample1[Warranty_date] ), FILTER ( ALLEXCEPT ( Sample1, Sample1[Account] ), Sample1[Index] = EARLIER ( Sample1[Index] ) - 1 ) ) Warranty_Changes = IF ( Sample1[Account] = BLANK (), "Off boarded", IF ( Sample1[Last_Mth_W_D] = BLANK () || Sample1[Last_Mth_W_D] = Sample1[Warranty_date], BLANK (), IF ( Sample1[Last_Mth_W_D] < Sample1[Warranty_date], "Cancelled", "Extended" ) ) )
Best regards,
Yuliana Gu
WoW!! this works perfectly. highly appreciated. I'll use these formulars across all my calculation.
Hi @shower999,
Please refer to below DAX formulas to add calculated columns:
Index = RANKX ( FILTER ( Sample1, Sample1[Account] = EARLIER ( Sample1[Account] ) ), Sample1[Data_date], , ASC, DENSE ) Last_Mth_Profile = CALCULATE ( SELECTEDVALUE ( Sample1[Profile] ), FILTER ( ALLEXCEPT ( Sample1, Sample1[Account] ), Sample1[Index] = EARLIER ( Sample1[Index] ) - 1 ) ) Profile_Changes = IF ( Sample1[Account] = BLANK (), "Off boarded", IF ( Sample1[Last_Mth_Profile] = BLANK () || Sample1[Last_Mth_Profile] = Sample1[Profile], BLANK (), IF ( Sample1[Last_Mth_Profile] < Sample1[Profile], "Up", "Down" ) ) ) Last_Mth_W_D = CALCULATE ( SELECTEDVALUE ( Sample1[Warranty_date] ), FILTER ( ALLEXCEPT ( Sample1, Sample1[Account] ), Sample1[Index] = EARLIER ( Sample1[Index] ) - 1 ) ) Warranty_Changes = IF ( Sample1[Account] = BLANK (), "Off boarded", IF ( Sample1[Last_Mth_W_D] = BLANK () || Sample1[Last_Mth_W_D] = Sample1[Warranty_date], BLANK (), IF ( Sample1[Last_Mth_W_D] < Sample1[Warranty_date], "Cancelled", "Extended" ) ) )
Best regards,
Yuliana Gu
WoW!! this works perfectly. highly appreciated. I'll use these formulars across all my calculation.
March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Prices go up Feb. 11th.
If you love stickers, then you will definitely want to check out our Community Sticker Challenge!
User | Count |
---|---|
142 | |
71 | |
64 | |
52 | |
50 |
User | Count |
---|---|
208 | |
91 | |
62 | |
59 | |
56 |