Show start date

[Jorgast]

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:

1. If the account has a setup code of 1 within 120 days and additional extra codes. Then i want the rows that dont have the code of 1 to show the transaction date if it is in within 120 days.
2. If the account does not have a setup code of 1 within 120 days then dont show anything.

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.

[v-shex-msft · Accepted 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