cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Jorgast Member
Member

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.

1 ACCEPTED SOLUTION

Accepted Solutions
Community Support Team
Community Support Team

Re: Show start date

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

Community Support Team _ Xiaoxin Sheng
If this post helps, please consider Accept it as the solution to help the other members find it more quickly
Learning resources: Power BI

View solution in original post

1 REPLY 1
Community Support Team
Community Support Team

Re: Show start date

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

Community Support Team _ Xiaoxin Sheng
If this post helps, please consider Accept it as the solution to help the other members find it more quickly
Learning resources: Power BI

View solution in original post

Helpful resources

Announcements
Exclusive LIVE Community Event #3 – Ask Arun Anything

Exclusive LIVE Community Event #3 – Ask Arun Anything

Join us in the third Triple A event!

Meet the 2020 Season 1 Power BI Super Users!

Meet the 2020 Season 1 Power BI Super Users!

It’s the start of a new Super User season! Learn all about the new Super Users and brand-new tiered recognition system.

January 2020 Community Highlights

January 2020 Community Highlights

Make sure you didn't miss any of the things that happened in the community in January!

Difinity Conference

Difinity Conference

The largest Power BI, Power Platform, and Data conference in New Zealand

Top Solution Authors
Top Kudoed Authors