Running total using mutiple filters

[Anonymous]

Hi I have following data 

Domian	Descr	Date	Count	Cumulative
A	Close	01/01/2020	1	1
B	Open	01/01/2020	0	0
C	Close	01/01/2020	1	1
A	Close	02/01/2020	2	3
A	Open	02/01/2020	1	1

I have 1st 4 columns and I have to calculate column 5 - Cumulative in the measure to use it for a line graph.

I have to add numbers in the cumulative column where Domain and descr are the same for the previous date . Eg A domian / clos e descr should show cumlative of A domain / close descr of o1/01/2020 and 02/01/2020. How can this be done?

I am not very good with the multiple filters in DAX , hence requesting help.

Thanks in advance!

[edhans · Accepted solution]

Hi @Anonymous - I love Power Query, but this is best done in DAX I believe as was your original course. Here is what I have done:

You can see it matches your expected "Cumulative Goal" values. Full measure text below.

Cumulative =
VAR varDomain =
    MAX( 'Table'[Domian] )
VAR varDescription =
    MAX( 'Table'[Descr] )
VAR varCurrentDate =
    MAX( 'Table'[Date] )
RETURN
    CALCULATE(
        SUM( 'Table'[Count] ),
        FILTER(
            ALL( 'Table' ),
            'Table'[Domian] = varDomain
                && 'Table'[Descr] = varDescription
                && 'Table'[Date] <= varCurrentDate
        )
    )

[edhans · Accepted solution]

Hi @Anonymous - I love Power Query, but this is best done in DAX I believe as was your original course. Here is what I have done:

You can see it matches your expected "Cumulative Goal" values. Full measure text below.

Cumulative =
VAR varDomain =
    MAX( 'Table'[Domian] )
VAR varDescription =
    MAX( 'Table'[Descr] )
VAR varCurrentDate =
    MAX( 'Table'[Date] )
RETURN
    CALCULATE(
        SUM( 'Table'[Count] ),
        FILTER(
            ALL( 'Table' ),
            'Table'[Domian] = varDomain
                && 'Table'[Descr] = varDescription
                && 'Table'[Date] <= varCurrentDate
        )
    )

[Anonymous]

This is what I did- different from yours, but worked

Running Total =
CALCULATE(
SUM('Table'[count]),
FILTER(
'Table',
'Table'[Domain]=EARLIER('Table'[Domain])
&& 'Table'[Descr]=EARLIER('Table'[Descr]) && 'Table'[Date] <= EARLIER('Table'[Date])

)
)

How this different than yours @edhans ?

P.S. Thanks for replying with a solutions

[edhans · Accepted solution]

Mine uses variables @Anonymous. The EARLIER() function is no longer needed and is not recommended. It returns the same results, and 3 years ago, was the only way to do it. But variables, the VAR/RETURN construct, is easier to read and edit, and can actually improve performance where you might user EARLIER() multiple times in a measure.

@Anonymous is correct that you should NOT use this in a calculated column. You should avoid them as a rule. 

You can do this in Power Query, but it is not easy, and must use the advanced editor. Here is the code:

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WclTSUXLOyS9OBdIGhvpAZGRgZADkGIJxrE60khOQ5V+QmoeuAoJBKpxhhijgNgXFIiMkJUZAbAxXArPICNOQWAA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Domian = _t, Descr = _t, Date = _t, Count = _t, Cumulative = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Date", type date}, {"Count", Int64.Type}, {"Cumulative", Int64.Type}}),
    #"Grouped Rows" = 
        Table.Group(
            #"Changed Type", 
            {"Domian", "Descr"}, 
            {
                {
                    "All Rows", 
                    each 
                        let
                            varCurrentTable = _
                        in
                        Table.AddColumn(
                            _,
                            "Cumulative Total",
                            each
                                let
                                    varCumulativeDate = [Date]
                                in
                                    List.Sum(
                                        Table.SelectRows(
                                            varCurrentTable,
                                            each [Date] <= varCumulativeDate
                                        )[Count]
                                    )
                        ), 
                    type table [Domian=nullable text, Descr=nullable text, Date=nullable date, Count=nullable number, Cumulative=nullable number, Cumulative Total = nullable number]
                }
            }
        ),
    #"Expanded All Rows" = Table.ExpandTableColumn(#"Grouped Rows", "All Rows", {"Date", "Count", "Cumulative", "Cumulative Total"}, {"Date", "Count", "Cumulative", "Cumulative Total"})
in
    #"Expanded All Rows"

If you need this in a column, then yes, by all means, use this code. It returns the red column, next to your "Cumulative" goal in the sample.

I cannot guarantee this will be performant over hundreds of thousands, or millions of records. That is where DAX excels and Power Query tends to bog down, but over 10K records or so, no problem. Give it a try.

I would appreciate KUDOS for any posts in this thread that have helped you, and if one of my answers is the solution, please mark it as such. See the very bottom of this post for some reasons not to use a calculated column. Below are directions to use this code in Power Query if you are not accustomed to pasting raw code in.

How to use M code provided in a blank query:
1) In Power Query, select New Source, then Blank Query
2) On the Home ribbon, select "Advanced Editor" button
3) Remove everything you see, then paste the M code I've given you in that box.
4) Press Done
5) See this article if you need help using this M code in your model.

In general, try to avoid calculated columns. There are times to use them, but it is rare. Getting data out of the source system, creating columns in Power Query, or DAX Measures are usually preferred to calculated columns. See these references:
Calculated Columns vs Measures in DAX
Calculated Columns and Measures in DAX
Storage differences between calculated columns and calculated tables
SQLBI Video on Measures vs Calculated Columns
Creating a Dynamic Date Table in Power Query

[Anonymous]

Thank u so much for taking out time to explain this!! 

[edhans]

Glad to help @Anonymous - hope the rest of your project runs smoothly!

[Anonymous]

There are always many ways to do it in PQ and some of them will be blazingly fast, some of them will drag on and on. To know which construct you should use when in order to squeeze maximum performance out of M (the PQ language), you should read about the philosophy of programming with M and when constructs get calculated. M is a functional language and has its own rules of evaluation. To know them thoroughly is the key to writing very fast M routines. You have to master the rules of lazy evaluation to get performant M code. And this code will always outperform DAX if it's properly written.

[Anonymous]

This is best done in DAX IF AND ONLY IF it's got to be a measure, @edhans. Actually, in case of a measure THERE IS NO CHOICE but DAX. But I think it was meant to be a calculated column, which PQ is best for.

[Anonymous]

can u explain  how can this be done in power query? Because currently I added a new column in dax 

[Anonymous]

Why don't you do this in Power Query? This is actually the proper way to do it. Not in DAX.

[Anonymous]

How can his be done in power query?