cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
IamJuan Frequent Visitor
Frequent Visitor

Extract the day on which a first transaction was generated

Hi Experts,

 

I need to extract the day on which the first transaction was generated. As shown below.

 

 

problemBI.PNG

 

I have tried for hours but I have not been able to find a way to do it. Could you please help me?

 

Thanks,  Smiley Happy

2 ACCEPTED SOLUTIONS

Accepted Solutions
Super User
Super User

Re: Extract the day on which a first transaction was generated

@IamJuan 

 

Without date hierarchy, you can use following

 

Calculated Table =
SUMMARIZE (
    ADDCOLUMNS (
        TableName,
        "First_Date", CALCULATE (
            MIN ( TableName[ProblemColumn] ),
            FILTER (
                TableName,
                MONTH ( TableName[ProblemColumn] )
                    = MONTH ( EARLIER ( TableName[ProblemColumn] ) )
                    && YEAR ( TableName[ProblemColumn] )
                        = YEAR ( EARLIER ( TableName[ProblemColumn] ) )
            )
        )
    ),
    [First_Date]
)
Super User
Super User

Re: Extract the day on which a first transaction was generated

@IamJuan 

 

Try following pattern

 

Calc Table =
SUMMARIZE (
    FILTER ( Table1, [User] = "J" ),
    [Date],
    [Company],
    [User],
    "CA", SUM ( Table1[CA] ),
    "LMS", SUM ( Table1[LMS] 
.........
and so on for other numeric columns

)
11 REPLIES 11
Super User
Super User

Re: Extract the day on which a first transaction was generated

Hi @IamJuan 

 

Try this calculated table

 

From the Modelling Tab>>New Table

 

Calculated Table =
SUMMARIZE (
    ADDCOLUMNS (
        TableName,
        "First_Date", CALCULATE (
            MIN ( TableName[ProblemColumn] ),
            ALLEXCEPT (
                TableName,
                TableName[ProblemColumn].[Month],
                TableName[ProblemColumn].[Year]
            )
        )
    ),
    [First_Date]
)
IamJuan Frequent Visitor
Frequent Visitor

Re: Extract the day on which a first transaction was generated

Hi @Zubair_Muhammad 

 

Thank you for your quick response.

 

I got the following error: Column reference to 'Date' in table 'F59D' cannot be used with a variation 'Month' because it does not have any.

 

I believe it is happing because I haven't created a Date Hierarchy breakdown the date in day, month and year.

But I do not how to do it.

 

The field date is a formula that converts Julian dates in calendar dates. So I can't find a way to create a hierarchy. When I tried I get the following:

 

dates.PNG


 I supposed it should look like this:

 

date 2.PNG

 

Do you know how can I fix it?

 

 Thank again Smiley Happy

 

 

Super User
Super User

Re: Extract the day on which a first transaction was generated

@IamJuan 

 

Without date hierarchy, you can use following

 

Calculated Table =
SUMMARIZE (
    ADDCOLUMNS (
        TableName,
        "First_Date", CALCULATE (
            MIN ( TableName[ProblemColumn] ),
            FILTER (
                TableName,
                MONTH ( TableName[ProblemColumn] )
                    = MONTH ( EARLIER ( TableName[ProblemColumn] ) )
                    && YEAR ( TableName[ProblemColumn] )
                        = YEAR ( EARLIER ( TableName[ProblemColumn] ) )
            )
        )
    ),
    [First_Date]
)
IamJuan Frequent Visitor
Frequent Visitor

Re: Extract the day on which a first transaction was generated

@Zubair_Muhammad ,Thank you so much!!

You are a genius Smiley Happy

IamJuan Frequent Visitor
Frequent Visitor

Re: Extract the day on which a first transaction was generated

Hi @Zubair_Muhammad ,

 

I have tried to summarise and move some columns from the problem to the calculated one but for some reason, I am not able to do it. Do you think you could help me?

 

What I am trying to achieve is the following :

 

On the problem table, I want to summarize the values for each month if they belong to the same company and they were created for the same user.

 

Problem tableproblem table v2.PNG

N.B. I know I can do it on the report using a measure but I really need those values in a column.

 

Calculated table (expected)

calculated table2.PNG

 

Thank you for all your help. It is much appreciated.

 

 

 

Super User
Super User

Re: Extract the day on which a first transaction was generated

@IamJuan 

 

Can you copy paste this data (Copiable format)?

IamJuan Frequent Visitor
Frequent Visitor

Re: Extract the day on which a first transaction was generated

HI @Zubair_Muhammad ,Thank you for getting back to me!! Sure I can

Here the data

 

DateCompanyCALMSTMSDSDSTYD1D2D3User
1/08/2018abc82609231386042249096230947118999316833477251575323j
1/08/2018abc268006120435402155070272013526020102079705655445317j
1/08/2018abc3932447646765264345473975008084742034925135153251318259-462709j
1/08/2018abc82609231386042249096230947118999316833477251575323a
1/08/2018def3932447646765264345473971254781125687135153251318259-462709j
2/08/2018abc493244764676526434547397154786123489523153252318259-4629j
1/08/2018def7260922138604424909613094758999316833477251575323j
1/08/2018def16800611043540115507017201356020102079705655445317j
1/08/2018def4932447636765264245473974008084721034925135153251318259-462709j

Second table

DateCompanyCALSMTMSDSDSTYD1D2D3User
1/08/2018abc42830629519474083895156353031929458269281374012814225381157931j
1/08/2018def51730629399474082995156341931929222269281374012814225381157931j

 

Thank you Smiley Happy 

Super User
Super User

Re: Extract the day on which a first transaction was generated

Hi @IamJuan 

 

Why is the following excluded from final output?

Why user "A" is not in final outpurt

 

Row # 5

1/08/2018 def 39324476 46765264 34547397 125478 1125687 13515325 1318259 -462709 j
IamJuan Frequent Visitor
Frequent Visitor

Re: Extract the day on which a first transaction was generated

Hi @Zubair_Muhammad 

 

Sorry, I missed the row 5. It should be include.

 

The expected table should be:

 

1/08/2018def9105510586712672644989604205740723352615272554532740797695222j

 

The only user that I am interested in is the j one. differents user need to be excluded

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.

Summit North America

Power Platform Summit North America

Register by September 5 to save $200

PBI Community Highlights

PBI Community Highlights

Check out what's new in the Power BI Community!

MBAS Gallery

Watch Sessions On Demand!

Continue your learning in our online communities.

Top Kudoed Authors
Users Online
Currently online: 379 members 3,660 guests
Please welcome our newest community members: