cancel
Showing results for
Did you mean:
Highlighted
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.

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

Thanks,

2 ACCEPTED SOLUTIONS

Accepted Solutions
Super User

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

Without date hierarchy, you can use following

```Calculated Table =
SUMMARIZE (
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

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

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

## 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 (
TableName,
"First_Date", CALCULATE (
MIN ( TableName[ProblemColumn] ),
ALLEXCEPT (
TableName,
TableName[ProblemColumn].[Month],
TableName[ProblemColumn].[Year]
)
)
),
[First_Date]
)
```
Frequent Visitor

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

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

Super User

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

Without date hierarchy, you can use following

```Calculated Table =
SUMMARIZE (
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]
)
```
Frequent Visitor

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

You are a genius

Frequent Visitor

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

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.

Super User

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

Can you copy paste this data (Copiable format)?

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

 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

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
Frequent Visitor

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

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

Announcements

#### 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 Platform Summit North America

Register by September 5 to save \$200

#### PBI Community Highlights

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

#### Watch Sessions On Demand!

Continue your learning in our online communities.

Top Kudoed Authors
Users Online
Currently online: 379 members 3,660 guests
Recent signins: