Forum Discussion
Creating a latest occurrence column
Hello fellow PBI'ers,
I am hoping to create a calculated column which identifies the latest occurrence with each account based on a few criterias. The result is as per the "Desired_Result" column in the mock table below:
Task:
- For each account, Identify the latest maintenance occurrence by end date for each product. The result should be a string.
| AccountName | EndDate | Family | Product | Desired_Result |
| Account 1 | 1-Jan-22 | Maintenance | A | Latest |
| Account 1 | 1-Mar-21 | Maintenance | A | |
| Account 1 | 1-Jul-22 | Software | A | |
| Account 2 | 1-Apr-20 | Software | B | |
| Account 2 | 1-Apr-22 | Software | C | |
| Account 3 | 1-Sep-21 | Maintenance | C | Latest |
| Account 4 | 1-Sep-22 | Maintenance | A | |
| Account 4 | 1-Nov-22 | Maintenance | A | Latest |
| Account 4 | 1-Jan-21 | Maintenance | B | Latest |
| Account 4 | 1-Aug-20 | Software | C | |
| Account 4 | 1-Aug-20 | Maintenance | C | Latest |
Would anyone be able to assist with this? I've tried the common SWITCH(TRUE() method but i can't seem to break it down to an products per account level...
Thanks!
Try this
_Latest Result Column= Var Acc = 'Latest Occurence'[AccountName] Var Pro = 'Latest Occurence'[Product] Var dt = CALCULATE(MAX('Latest Occurence'[EndDate ]),FILTER('Latest Occurence','Latest Occurence'[AccountName]=Acc && 'Latest Occurence'[Product]=Pro && 'Latest Occurence'[Family]="Maintenance")) RETURN IF(dt='Latest Occurence'[EndDate ] && 'Latest Occurence'[Family]="Maintenance","Latest","")
3 Replies
Amazing, thank you both Jihwan_Kim & FarhanAhmed ! Both measures work as it should and has saved my hair. True life saver 🙂
Hi,
Please check the below picture and the attached pbix file.
Desired Result CC =
IF (
NOT ( Data[Family] = "Maintenance" ),
BLANK (),
SWITCH (
TRUE (),
VAR currentaccountname = Data[AccountName]
VAR currentproduct = Data[Product]
VAR newtable =
FILTER (
Data,
Data[AccountName] = currentaccountname
&& Data[Product] = currentproduct
&& Data[Family] = "Maintenance"
)
VAR maxdate_newtable =
MAXX ( newtable, Data[EndDate] )
RETURN
Data[EndDate] = maxdate_newtable, "Latest"
)
)Try this
_Latest Result Column= Var Acc = 'Latest Occurence'[AccountName] Var Pro = 'Latest Occurence'[Product] Var dt = CALCULATE(MAX('Latest Occurence'[EndDate ]),FILTER('Latest Occurence','Latest Occurence'[AccountName]=Acc && 'Latest Occurence'[Product]=Pro && 'Latest Occurence'[Family]="Maintenance")) RETURN IF(dt='Latest Occurence'[EndDate ] && 'Latest Occurence'[Family]="Maintenance","Latest","")
