Dynamic aggregation and calculation

[Anonymous]

Hello guys

I am trying to translate a nested sql query on power bi and keep the ability to use the first level fields as slicers

Here is the scenario

The raw data looks like this

customer id	order_number	country	amount	date
A	1	FR	100	01/01/2020
A	1	FR	100	01/02/2020
A	1	FR	100	01/03/2020
A	2	UK	100	01/01/2020
A	2	UK	100	01/02/2020
B	3	US	100	01/02/2020
B	3	US	100	01/03/2020
B	4	FR	100	01/02/2020
B	4	FR	100	01/03/2020
C	5	US	100	01/02/2020
C	5	US	100	01/03/2020

For my calculation i need to make a first group by and move from order_number to customer_id level  and it should give this

customer id	date	first sum	RowNumber	is_new_customer
A	01/01/2020	200	1	1
A	01/02/2020	200	2	0
A	01/03/2020	100	3	0
B	01/02/2020	200	1	1
B	01/03/2020	200	2	0
C	01/02/2020	100	1	1
C	01/03/2020	100	2	0

I have also added a row number to help me count the new customer (where row_number = 1 )

And my final need should be like this

DATE	count new customer	total customer	second sum
01/01/2020	1	1	200
01/02/2020	2	3	500
01/03/2020	0	3	400

The challenge here is to create these measures and be able to filter on the country

If I select "US" as a country I should have this result

DATE	count new customer	total customer	second sum
01/01/2020	0	0	0
01/02/2020	2	2	200
01/03/2020	0	2	200

I tried with a calculate table, but it is static and I can't use the filters of the first level

I have tried to create a dynamic measure where I have built the last aggregation table to countx the new customers, but its not filtring on the row_number = 1, plus its a horible dax code, I am new on power bi and I dont even know if I am on the right track

here is the code

count new customer = 
-- var v = filter(
    VAR a =
        ADDCOLUMNS (
            SUMMARIZE (
                ADDCOLUMNS (
                    GROUPBY (
                        'Fact  flat';
                        'Fact  flat'[account_id];
                        'Fact  flat'[po_number];
                        'Fact  flat'[the_month];
                        'Fact  flat'[continuity];
                        'Fact  flat'[mrr_transposed]
                    );
                    "continuity_new_customers"; IF ( 'Fact  flat'[continuity] = 0; 1; 'Fact  flat'[continuity] )
                );
                [account_id];
                [the_month];
                [continuity_new_customers];
                "sum mrr trans"; SUM ( 'Fact  flat'[mrr_transposed] )
            );
            "RowNumberEmbed"; RANKX (
                FILTER (
                    GROUPBY ( 'Fact  flat'; 'Fact  flat'[account_id]; 'Fact  flat'[the_month] );
                    'Fact  flat'[account_id] = EARLIER ( 'Fact  flat'[account_id] )
                );
                [the_month];
                ;
                ASC
            )
        )

    var b = 
        ADDCOLUMNS( 
            a;
            "Previous_Value"; CALCULATE (
        
                MAxX(      SUMMARIZE (
                                            GROUPBY (
                                                'Fact  flat';
                                                'Fact  flat'[account_id];
                                                'Fact  flat'[po_number];
                                                'Fact  flat'[the_month];
                                                'Fact  flat'[mrr_transposed]
                                            );      
                                        [account_id];
                                        [the_month];
                                        "sum mrr trans"; SUM ( 'Fact  flat'[mrr_transposed] )
                                    )          
                                    ; [sum mrr trans] )

    
                    ; FILTER ( a; [account_id]= EARLIER([account_id])  && [the_month] <   EARLIER([the_month]) && [RowNumberEmbed] = EARLIER([RowNumberEmbed]  ) -1
            ))

        )


var c = 
    filter(
             ADDCOLUMNS(b;
                    "row_number_new_customer"; if([Previous_Value] <>0 ; [RowNumberEmbed];1))
        ; [RowNumberEmbed] = 1
        ) 
return  COUNTX(c;[account_id])

Can you please help me on this one please, this is the third week where i am stuck on this task

Thank you in advance for your help

[Greg_Deckler · Accepted solution]

Here is second sum. PBIX with all three measures is included as an attachment below.

second sum = 
    VAR __Date = MAX('Table'[date])
    VAR __Country = SELECTCOLUMNS('Table',"Country",[country])
    VAR __Table = 
        SUMMARIZE(
            FILTER(
                ALL('Table'),
                [country] IN __Country
            ),
            [date],
            "Sum",SUM('Table'[amount])
        )
    VAR __Sum = SUMX(FILTER(__Table,[date] = __Date),[Sum])
RETURN
    IF(ISBLANK(__Sum),0,__Sum)

[Anonymous]

Thx a lot @Greg_Deckler  !!! 😄

[Greg_Deckler]

Happy to help! 🙂

[Greg_Deckler]

Total customer:

total customer = 
    VAR __Date = MAX('Table'[date])
    VAR __Country = SELECTCOLUMNS('Table',"Country",[country])
    VAR __Table = 
        SUMMARIZE(
            FILTER(
                ALL('Table'),
                [country] IN __Country
            ),
            [customer id],
            "Date",MIN('Table'[date])
        )
    VAR __Count = COUNTROWS(FILTER(__Table,[Date]<=__Date))
RETURN
    IF(ISBLANK(__Count),0,__Count)

[Greg_Deckler]

Here is count new customer:

count new customer = 
    VAR __Date = MAX('Table'[date])
    VAR __Country = SELECTCOLUMNS('Table',"Country",[country])
    VAR __Table = 
        SUMMARIZE(
            FILTER(
                ALL('Table'),
                [country] IN __Country
            )
            ,[customer id],"Date",MIN('Table'[date])
        )
    VAR __Count = COUNTROWS(FILTER(__Table,[Date] = __Date))
RETURN
    IF(ISBLANK(__Count),0,__Count)