Forum Discussion
Lookupvalue Last Quarter in the same table without aggregation
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 |
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.
2 Replies
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.