Extract the day on which a first transaction was generated

[Anonymous]

Hi Experts,

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

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

Thanks, 

[Zubair_Muhammad · Accepted solution]

@Anonymous 

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]
)

[Zubair_Muhammad]

Hi @Anonymous 

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]
)

[Anonymous]

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:

 I supposed it should look like this:

Do you know how can I fix it?

 Thank again 

[Zubair_Muhammad · Accepted solution]

@Anonymous 

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]
)

[Anonymous]

@Zubair_Muhammad ,Thank you so much!!

You are a genius 

[Anonymous]

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 table

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)

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

[Zubair_Muhammad]

@Anonymous 

Can you copy paste this data (Copiable format)?

[Anonymous]

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

Here the data

Date	Company	CA	LMS	TMS	DS	DSTY	D1	D2	D3	User
1/08/2018	abc	826092	3138604	2249096	230947	1189993	16833	47725	1575323	j
1/08/2018	abc	2680061	2043540	2155070	2720135	2602010	207970	56554	45317	j
1/08/2018	abc	39324476	46765264	34547397	50080847	42034925	13515325	1318259	-462709	j
1/08/2018	abc	826092	3138604	2249096	230947	1189993	16833	47725	1575323	a
1/08/2018	def	39324476	46765264	34547397	125478	1125687	13515325	1318259	-462709	j
2/08/2018	abc	49324476	46765264	34547397	154786	1234895	23153252	318259	-4629	j
1/08/2018	def	726092	2138604	4249096	130947	589993	16833	47725	1575323	j
1/08/2018	def	1680061	1043540	1155070	1720135	602010	207970	56554	45317	j
1/08/2018	def	49324476	36765264	24547397	40080847	21034925	13515325	1318259	-462709	j

Second table

Date	Company	CA	LSM	TMS	DS	DSTY	D1	D2	D3	User
1/08/2018	abc	42830629	51947408	38951563	53031929	45826928	13740128	1422538	1157931	j
1/08/2018	def	51730629	39947408	29951563	41931929	22226928	13740128	1422538	1157931	j

Thank you 🙂 

[Zubair_Muhammad]

Hi @Anonymous 

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

[Anonymous]

Hi @Zubair_Muhammad 

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

The expected table should be:

1/08/2018

def

91055105

86712672

64498960

42057407

23352615

27255453

2740797

695222

j

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

[Zubair_Muhammad · Accepted solution]

@Anonymous 

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

)

[Anonymous]

Thank you, I will try straight away 🙂