Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Anonymous
Not applicable

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

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

Regards
Zubair

Please try my custom visuals

View solution in original post

@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

)

Regards
Zubair

Please try my custom visuals

View solution in original post

11 REPLIES 11
Zubair_Muhammad
Community Champion
Community Champion

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

Regards
Zubair

Please try my custom visuals
Anonymous
Not applicable

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

 

 

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

Regards
Zubair

Please try my custom visuals
Anonymous
Not applicable

@Zubair_Muhammad ,Thank you so much!!

You are a genius Smiley Happy

Anonymous
Not applicable

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.

 

 

 

@Anonymous 

 

Can you copy paste this data (Copiable format)?


Regards
Zubair

Please try my custom visuals
Anonymous
Not applicable

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 🙂 

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

Regards
Zubair

Please try my custom visuals
Anonymous
Not applicable

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

@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

)

Regards
Zubair

Please try my custom visuals
Anonymous
Not applicable

Thank you, I will try straight away 🙂

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.