Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
6 years ago
Solved

Intelligent Headers DAX

Hi @all,

 

For our report I have following challenge.

 

For every month I need a sum of forcast. Because there is no column with a date available I only have 

the column headers which indicates the month. [See below]

 

Is there a way that a DAX formular reads these headers and compair it with a time intelligence function in order to return the forcast value of the current month.

 

As far as I learned the table is not stable so that the column positions are changing, a VLOOKUP might not be possible.

 

Item

Forcast SEP

Forcast OCT

A

123

321

B

456

654

C

789

321

 

Thank you so much for your help in advance.

 

Felix

Need Help
  • v-juanli-msft's avatar
    v-juanli-msft
    6 years ago

    Hi Anonymous 

    Add custom columns in Edit queries,

    et
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WclTSUTI0MgaSxkaGSrE60UpOQLaJqRmQNDM1AYs4A9nmFpYwNbEA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [Item = _t, #"Forcast SEP" = _t, #"Forcast OCT" = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Item", type text}, {"Forcast SEP", Int64.Type}, {"Forcast OCT", Int64.Type}}),
    #"Unpivoted Other Columns" = Table.UnpivotOtherColumns(#"Changed Type", {"Item"}, "Attribute", "Value"),
    #"Added Conditional Column" = Table.AddColumn(#"Unpivoted Other Columns", "Month", each if Text.Contains([Attribute], "SEP") then 9 else if Text.Contains([Attribute], "OCT") then 10 else null),
    #"Added Custom" = Table.AddColumn(#"Added Conditional Column", "YEAR/MONTH", each Text.Combine({Text.Combine({Text.From(Date.Year(DateTime.LocalNow()), "en-US"), Text.From([Month], "en-US")}, "/"),"1"},"/")),
    #"Changed Type1" = Table.TransformColumnTypes(#"Added Custom",{{"YEAR/MONTH", type date}})
in
    #"Changed Type1"

    Close&&apply

    Create visuals

     

    Best Regards
    Maggie
    Community Support Team _ Maggie Li
    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

1 Reply

  • v-juanli-msft's avatar
    v-juanli-msft
    Community Support
    6 years ago

    Hi Anonymous 

    Add custom columns in Edit queries,

    et
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WclTSUTI0MgaSxkaGSrE60UpOQLaJqRmQNDM1AYs4A9nmFpYwNbEA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [Item = _t, #"Forcast SEP" = _t, #"Forcast OCT" = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Item", type text}, {"Forcast SEP", Int64.Type}, {"Forcast OCT", Int64.Type}}),
    #"Unpivoted Other Columns" = Table.UnpivotOtherColumns(#"Changed Type", {"Item"}, "Attribute", "Value"),
    #"Added Conditional Column" = Table.AddColumn(#"Unpivoted Other Columns", "Month", each if Text.Contains([Attribute], "SEP") then 9 else if Text.Contains([Attribute], "OCT") then 10 else null),
    #"Added Custom" = Table.AddColumn(#"Added Conditional Column", "YEAR/MONTH", each Text.Combine({Text.Combine({Text.From(Date.Year(DateTime.LocalNow()), "en-US"), Text.From([Month], "en-US")}, "/"),"1"},"/")),
    #"Changed Type1" = Table.TransformColumnTypes(#"Added Custom",{{"YEAR/MONTH", type date}})
in
    #"Changed Type1"

    Close&&apply

    Create visuals

     

    Best Regards
    Maggie
    Community Support Team _ Maggie Li
    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.