Create new column based conditions of other two columns

[rschaudhr]

Date	Column A	Column B	Column D - new column
1/2/2019	100	1%	100
1/3/2019	200	0%	0
1/4/2019	100	0%	0
1/5/2019	300	2%	600
1/6/2019	100	.1%	100
1/7/2019	50	0%	0
1/8/2019	50	0%	0
1/9/2019	100	0%	0
1/10/2019	200	3%	400

The above table show Date, column A and colum B as current fields. Column C would be the DAX calculated column. So, the logic is that if column B has <>0 value that it will take the value in column A and store it in column C. If value in column B is 0 then it will store a 0 value in column C. However, it should sum up all values in column A and store and add it to value in Column A where column B is <> 0.  for example from 1/7/2019 to 1/9/2019 column C is showing 0. However, on 1/10/2019 column C is showing 400. That is because it is summing values in Column A from 1/7/2019 to 1/9/2019 and adding it to value on 1/10/2019 and showing the result in column C. 

Thank you in advance.

[edhans · Accepted solution]

The following should work @rschaudhr 

Column C =
VAR varCurrentDate = 'Table'[Date]
VAR varStartDate =
    MAXX(
        FILTER(
            'Table',
            'Table'[Date] < varCurrentDate
                && 'Table'[Column B] <> 0
        ),
        'Table'[Date]
    )
VAR varTotal =
    SUMX(
        FILTER(
            'Table',
            'Table'[Date] > varStartDate
                && 'Table'[Date] <= varCurrentDate
        ),
        'Table'[Column A]
    )
VAR Result =
    IF(
        'Table'[Column B] <> 0,
        varTotal,
        0
    )
RETURN
    Result

[rschaudhr · Accepted solution]

Hi edhans,

This solution worked very well! Thank you!

I have another question. If I want to incorporate this same logic in Transform Data section, how would I do that. 

thanks.  

[edhans · Accepted solution]

The following should work @rschaudhr 

Column C =
VAR varCurrentDate = 'Table'[Date]
VAR varStartDate =
    MAXX(
        FILTER(
            'Table',
            'Table'[Date] < varCurrentDate
                && 'Table'[Column B] <> 0
        ),
        'Table'[Date]
    )
VAR varTotal =
    SUMX(
        FILTER(
            'Table',
            'Table'[Date] > varStartDate
                && 'Table'[Date] <= varCurrentDate
        ),
        'Table'[Column A]
    )
VAR Result =
    IF(
        'Table'[Column B] <> 0,
        varTotal,
        0
    )
RETURN
    Result

[rschaudhr]

Thank you for your help. The first solution is really good. The next one is a good starting point. 

[rschaudhr]

@edhans 

Just to let you know that your second solution is m query also worked out. My dataset was about 5K so it worked out well. The only downside to this is that it takes a long time to process. However it worked.

Thanks

[edhans]

Yeah, 5K-10K records, not too bad. 100K, a pain. 1M? Unworkable. There are some things DAX is better at, and table scanning is one of them. It could do 1 billion records just as fast as 1 million.

[edhans]

great @rschaudhr - let me know how the Power Query solution works for you. I am curious over large record sets how it performs on refresh.

[rschaudhr · Accepted solution]

Hi edhans,

This solution worked very well! Thank you!

I have another question. If I want to incorporate this same logic in Transform Data section, how would I do that. 

thanks.  

[edhans · Accepted solution]

You mean in Power Query? Just so you know, I am heavily heavily biased in providing answers in Power Query vs Calculated columns (links below) unless it doesn't make sense. And in this case, I do not think it does. This is an analysis that, IMHO, is best done in DAX using a calculated column or a measure. Power Query doesn't do well when scanning large tables "filtering" on the fly like this. For 10,000 rows, it doesn't matter. For 1,000,000 rows, Power Query may never finish. I am trying to think of a way using grouping or row selection that would avoid this but am coming up blank. 

While I am not a fan of calculated columns, this is one of those instances where I think it will perform better than Power Query - unless someone with more skill than I (or imaginiation) can come up with a way. That said, this will work, but I will not swear it will perform well on large data sets. Test it and let me know. If you like it, it is preferable to Calculated Columns, all other things being equal.

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMtQ30jcyMLRU0lEyNDAAkapKsTogcWOYuBFY3AAmboKqHi5uChM3BosbwcTN0NTrGcK1mMOkTFFMssAubInDYkMDVJcaAyViAQ==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Date = _t, #"Column A" = _t, #"Column B" = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Date", type date}, {"Column A", Int64.Type}, {"Column B", Percentage.Type}}),
    RowData = 
        Table.AddColumn(
            #"Changed Type",
            "Current Data",
            each
                let
                    varCurrentDate = [Date],
                    varPreviousDate =
                        List.Max(
                            Table.SelectRows(
                                #"Changed Type",
                                each ([Date] < varCurrentDate and [Column B] <> 0)
                            )[Date]
                        )
                in
                if [Column B] = 0 then 0
                else
                List.Sum(
                    Table.SelectRows(
                        #"Changed Type",
                        each ([Date] = varCurrentDate) or ([Date] < varCurrentDate and [Date] > varPreviousDate and [Column B] = 0)
                    )[Column A]
                ),
                Int64.Type
        )
in
    RowData

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

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.

Please mark one or both as solutions, give kudos to thing that help, etc. 😁
I am most interested on the Power Query code on your full data set and how it performs.

[mhossain]

@rschaudhr 

What is the objective of your task, do you want to show those cumulative numbers by date in the Table visual? If so then cumulatative dax will work with criteria. Let me know why do you want calculate column in your table and not measure?

[rschaudhr]

Yes, it is cummulative based on Date. However, the condition are set based on column B.