Forum Discussion

Jorgast's avatar
Jorgast
Icon for Resolver II rankResolver II
7 years ago
Solved

Show start date

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 NameZIPAcctBuildingLocationCodeTransaction__TEST COLUMN
Company 14840201234/22/2019BLANK
Company 14840201235/3/2019BLANK
Company 14840201284/22/2019BLANK
Company 14840201285/3/2019BLANK
Company 14840201285/3/2019BLANK
Company 24264271114/22/2019BLANK
Company 24264271214/22/2019BLANK
Company 24264271115/9/20194/22/2019
Company 24264271255/9/20194/22/2019
Company 24264271185/15/20194/22/2019
Company 24264271185/15/20194/22/2019
Company 24264271285/15/20194/22/2019
Company 24264271285/15/20194/22/2019

 

Am i using the "Earlier" function in the right? Any guidance you can provide would be appreciated.

Need Help
  • Anonymous's avatar
    Anonymous
    7 years ago

    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

1 Reply

  • Anonymous's avatar
    Anonymous
    Not applicable
    7 years ago

    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