Getting the previoud row of a dynamic table

[Anonymous]

Hello everyone,

I am trying to understand how the groupBy and summarize function works, in order to create a measure based on a dynamic calculated table, I am doing well, but actually I am stuck on how to get the value of a previous row within the "group by" temp table.

Here is the a sample of raw data

The structure of the measure will look like :

Sumx (

                Filter(

                   summarize(

                                     nested group by

                                     )

                  ;filter conditions

                 )

        ;columns to sum

       )

The first step is to create the temp table to apply the sum on it, which i already did

But I still missing a column to complete this task, so my question is how to add a column with the value of the previous row for the sum_mrr_trans, I need it for the sum conditions

I have tryed to use the earlier function but i cant point on the temp table columns

Thank you in advance for your answers !  🙂

[v-gizhi-msft]

Hi,

Please try this calculated table formula:

Table =
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
        )
    )
RETURN
    ADDCOLUMNS (
        a,
        "Previous Value", CALCULATE (
            SUM ( 'Fact flat'[mrr_transposed] ),
            FILTER ( a, [RowNumberEmbed] = MAXX ( a, [RowNumberEmbed] ) - 1 )
        )
    )

Hope this helps.

Best Regards,

Giotto

[Anonymous · Accepted solution]

Hello Again !! 😄
First of all, thank you all for your replies, it helped me a lot.

I found a way to do it by combining the answer of @Greg_Deckler and @v-gizhi-msft 

tempTable = 
       
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
        )
    )

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

 So I had to use another summarize in the last calculation with the correct granularity (same of the first table), cause if i use a simple sum, it will not be applyed on the account level but on the po_number, and return a false result

Hello @Anonymous, thx for your reply, In this case I can't use  the power query, it ll create a static pre calculated table, which i wanted to avoid because i need to filter with a slicer on the lowest granularity "po_number" before the first "groupby"

so the measure will calculate it dynamically. @Greg_Deckler  explained very well the power of the measure for this kind of uses case in this ARTICLE (thx a lot for this one ! 🙂  )

If you have any other tips to optimize the code, I ll take them ! 😄

Cheers

[Greg_Deckler]

Well, for performance optimization you can reference my series here:

https://community.powerbi.com/t5/Community-Blog/Performance-Tuning-DAX-Part-1/ba-p/976275

There are 4 parts.

[Anonymous]

Hi @Anonymous.

I can't imagine how you would not be able to create such a table in Power Query. Tables created using DAX are STATIC as well in exactly the same way as the PQ ones and besides, Power Query is more powerful than DAX with respect to shaping data and is MUUUUUCH easier to document. Adding an index in PQ is a one-click operation. In DAX you have to create such monsters as

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

Moreover, creating complex DAX is not something I'd ever recommend unless you like wasting time deciphering such code after a month or two...

But, of course, you and your peers will have to maintain the monster. It's your call 🙂

Best
D

[Anonymous]

Such things should be performed in the right tool for this kind of manipulation - Power Query. DAX is Data Analysis eXpressions language, not a mashup language like M. Please use Power Query and make your (and the ones' that will have to maintain this) life easier.

Best
D

[Greg_Deckler]

Use yet another ADDCOLUMNS and in this row use something like: RowNumberEmbed = EARLIER(RowNumberEmbed) - 1