cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
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, then please consider Accept it as the solution to help the other members find it more quickly.



For learning resources/Release notes, please visit: | |
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, then please consider Accept it as the solution to help the other members find it more quickly.



For learning resources/Release notes, please visit: | |

Helpful resources

Announcements
Virtual Launch Event

Microsoft Business Applications October Virtual Launch Event

Join us for an in-depth look at the new innovations across Dynamics 365 and the Microsoft Power Platform.

Power BI Helps Homeless and Trouble Youth

Power BI Helps Homeless and Trouble Youth

We spoke with Power BI Super User, Greg Deckler, about his charity work

MBAS Gallery

Watch Sessions On Demand!

Continue your learning in our online communities.

Users Online
Currently online: 43 members 1,128 guests
Please welcome our newest community members: