Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
Hello Fellow Power BI users,
I am trying to find the origination date of an account based on the code that was put in. My date range is only 120 days in the past. I have 2 scenarios:
Here is the code i am trying to use to suit my needs which came from my previous posting.
https://community.powerbi.com/t5/Desktop/Finding-Max-value-in-a-group/m-p/316980#M140893
__TEST COLUMN= IF(‘TABLE1'[Code]<>"1",
CALCULATE( FIRSTDATE('TABLE1'[Transaction]),
FILTER('TABLE1',
'TABLE1'[Building]=EARLIER('TABLE1'[Building]) &&
'TABLE1'[LOCATION]=EARLIER('TABLE1'[LOCATION]) &&
'TABLE1'[COMPANY NAME]=EARLIER('TABLE1'[COMPANY NAME]) &&
'TABLE1'[ACCOUNT]=EARLIER('TABLE1'[ACCOUNT]) &&
'TABLE1'[Zip]=EARLIER('TABLE1'[Zip]) &&
-EARLIER('TABLE1'[Code]) = "1" &&
DATEDIFF(EARLIER('TABLE1'[Transaction]),'TABLE1'[Transaction],DAY) < 120
)),1/1/3000)
Here is my test table to illustrate the results i am trying achieve.
Company Name | ZIP | Acct | Building | Location | Code | Transaction | __TEST COLUMN |
Company 1 | 4840 | 20 | 1 | 2 | 3 | 4/22/2019 | BLANK |
Company 1 | 4840 | 20 | 1 | 2 | 3 | 5/3/2019 | BLANK |
Company 1 | 4840 | 20 | 1 | 2 | 8 | 4/22/2019 | BLANK |
Company 1 | 4840 | 20 | 1 | 2 | 8 | 5/3/2019 | BLANK |
Company 1 | 4840 | 20 | 1 | 2 | 8 | 5/3/2019 | BLANK |
Company 2 | 4264 | 27 | 1 | 1 | 1 | 4/22/2019 | BLANK |
Company 2 | 4264 | 27 | 1 | 2 | 1 | 4/22/2019 | BLANK |
Company 2 | 4264 | 27 | 1 | 1 | 1 | 5/9/2019 | 4/22/2019 |
Company 2 | 4264 | 27 | 1 | 2 | 5 | 5/9/2019 | 4/22/2019 |
Company 2 | 4264 | 27 | 1 | 1 | 8 | 5/15/2019 | 4/22/2019 |
Company 2 | 4264 | 27 | 1 | 1 | 8 | 5/15/2019 | 4/22/2019 |
Company 2 | 4264 | 27 | 1 | 2 | 8 | 5/15/2019 | 4/22/2019 |
Company 2 | 4264 | 27 | 1 | 2 | 8 | 5/15/2019 | 4/22/2019 |
Am i using the "Earlier" function in the right? Any guidance you can provide would be appreciated.
Solved! Go to Solution.
HI @Jorgast ,
You can use following calculate column formula if it suitable for your requirement:
First Date = VAR _filted = FILTER ( ALL ( Table2 ), [Company Name] = EARLIER ( [Company Name] ) && [ZIP] = EARLIER ( [ZIP] ) && [Building] = EARLIER ( [Building] ) && [Location] = EARLIER ( [Location] ) ) VAR minCode1 = MINX ( FILTER ( _filted, [Code] = 1 ), [Transaction] ) VAR minOther = MINX ( FILTER ( _filted, [Code] <> 1 ), [Transaction] ) VAR diff = DATEDIFF ( MIN ( minCode1, minOther ), MAX ( minCode1, minOther ), DAY ) RETURN IF ( 1 IN SELECTCOLUMNS ( _filted, "Code", [Code] ), IF ( diff < 120, MIN ( minCode1, minOther ) ) )
Regards,
Xiaoxin Sheng
HI @Jorgast ,
You can use following calculate column formula if it suitable for your requirement:
First Date = VAR _filted = FILTER ( ALL ( Table2 ), [Company Name] = EARLIER ( [Company Name] ) && [ZIP] = EARLIER ( [ZIP] ) && [Building] = EARLIER ( [Building] ) && [Location] = EARLIER ( [Location] ) ) VAR minCode1 = MINX ( FILTER ( _filted, [Code] = 1 ), [Transaction] ) VAR minOther = MINX ( FILTER ( _filted, [Code] <> 1 ), [Transaction] ) VAR diff = DATEDIFF ( MIN ( minCode1, minOther ), MAX ( minCode1, minOther ), DAY ) RETURN IF ( 1 IN SELECTCOLUMNS ( _filted, "Code", [Code] ), IF ( diff < 120, MIN ( minCode1, minOther ) ) )
Regards,
Xiaoxin Sheng
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
114 | |
99 | |
83 | |
70 | |
60 |
User | Count |
---|---|
150 | |
115 | |
104 | |
89 | |
65 |