Newbie Question : DAX to compare the last not empty column against other column

[arlequin71]

Hello, anyone can help me on this....

I have 4 columns displaying 4 Weekly Estimates that shoud be compared sequencially during the month against the Forecast column.  I need to write a dax (or another method) to select the Last non blank column (or measure) to compare it with the Forecast.

Thanks in advance for your help.

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WCk7MSS1WCEotMFTSUTIyAAIQbQSloXwQitVBUqxgBBQyhqm2hNKmOFQbA0UMzSCShuaEjDYByZqjGmmMU7UpSJUZmrMNkVTHAgA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [#"Sales Rep" = _t, Forecast = _t, #"Estimate 1" = _t, #"Estimate 2" = _t, #"Estimate 3" = _t, #"Estimate 4" = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Sales Rep", type text}, {"Forecast", Int64.Type}, {"Estimate 1", Int64.Type}, {"Estimate 2", Int64.Type}, {"Estimate 3", type text}, {"Estimate 4", type text}})
in
    #"Changed Type"

[v-shex-msft · Accepted solution]

Hi @arlequin71 ,

You can do unpivot columns on your query table columns, then you can simply write a formula to find out last non blank estimates value based on current sales rep group.

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WCk7MSS1WCEotMFTSUTIyAAIQbQSloXwQitVBUqxgBBQyhqm2hNKmOFQbA0UMzSCShuaEjDYByZqjGmmMU7UpSJUZmrMNkVTHAgA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [#"Sales Rep" = _t, Forecast = _t, #"Estimate 1" = _t, #"Estimate 2" = _t, #"Estimate 3" = _t, #"Estimate 4" = _t]),
    #"Unpivoted Columns" = Table.UnpivotOtherColumns(Source, {"Sales Rep", "Forecast"}, "Estimate", "Value"),
    #"Replaced Value" = Table.ReplaceValue(#"Unpivoted Columns","Estimate ","",Replacer.ReplaceText,{"Estimate"}),
    #"Changed Type" = Table.TransformColumnTypes(#"Replaced Value",{{"Sales Rep", type text}, {"Forecast", Int64.Type}, {"Estimate", Int64.Type},{"Value", type text}})
in
    #"Changed Type"

Regards,

Xiaoxin Sheng

[v-shex-msft · Accepted solution]

Hi @arlequin71 ,

You can do unpivot columns on your query table columns, then you can simply write a formula to find out last non blank estimates value based on current sales rep group.

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WCk7MSS1WCEotMFTSUTIyAAIQbQSloXwQitVBUqxgBBQyhqm2hNKmOFQbA0UMzSCShuaEjDYByZqjGmmMU7UpSJUZmrMNkVTHAgA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [#"Sales Rep" = _t, Forecast = _t, #"Estimate 1" = _t, #"Estimate 2" = _t, #"Estimate 3" = _t, #"Estimate 4" = _t]),
    #"Unpivoted Columns" = Table.UnpivotOtherColumns(Source, {"Sales Rep", "Forecast"}, "Estimate", "Value"),
    #"Replaced Value" = Table.ReplaceValue(#"Unpivoted Columns","Estimate ","",Replacer.ReplaceText,{"Estimate"}),
    #"Changed Type" = Table.TransformColumnTypes(#"Replaced Value",{{"Sales Rep", type text}, {"Forecast", Int64.Type}, {"Estimate", Int64.Type},{"Value", type text}})
in
    #"Changed Type"

Regards,

Xiaoxin Sheng

[arlequin71 · Accepted solution]

Thanks a Lot... it works!