Summarizing Hours in Date Columns

MelQuick you should unpivot your data in power query

- transform data
- select the first two columns in your table
- right-click, unpivot other columns it will add two columns, attribute, and value, rename these as per your requirement
- close and apply

To visualize,
- matrix visual:
- add an attribute on rows,
- add value on values section

Although if you want to work with date, it is recommended to add a date dimension and check my blog on how to add one.

Check my latest blog post Compare Budgeted Scenarios vs. Actuals I would ❤ Kudos if my solution helped. 👉 If you can spend time posting the question, you can also make efforts to give Kudos to whoever helped to solve your problem. It is a token of appreciation!

⚡Visit us at https://perytus.com, your one-stop-shop for Power BI-related projects/training/consultancy.⚡

Hi MelQuick ,

As parry2k suggested, please firstly transformed your table.

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("nZFbCgMhDEX34ve05GF8LKLQ/8H9b6MmIthicRwwB1Hu9Saep3u/HoDucCBSiRnhqRuoJbgLcOUYLANXelSvUCtdrtGFqOchr3YwgSzQ/TQPRpPoQ+s1CiE3Oft2OU2ywlcSjkryeT8LKimGdmcj2kX303YQgtF606goN9AdSU0oWXNXWvuVskkzp3HQ8u/f52elfAA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"WBS/CTR" = _t, PAAF = _t, #"12/31/20" = _t, #"01/01/21" = _t, #"01/08/21" = _t, #"01/15/21" = _t, #"01/22/21" = _t, #"01/29/21" = _t, #"02/05/21" = _t, #"02/12/21" = _t, #"02/19/21" = _t, #"02/26/21" = _t, #"03/05/21" = _t, #"03/12/21" = _t, #"03/19/21" = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"WBS/CTR", type text}, {"PAAF", Int64.Type}, {"12/31/20", type number}, {"01/01/21", Int64.Type}, {"01/08/21", Int64.Type}, {"01/15/21", Int64.Type}, {"01/22/21", Int64.Type}, {"01/29/21", Int64.Type}, {"02/05/21", Int64.Type}, {"02/12/21", Int64.Type}, {"02/19/21", Int64.Type}, {"02/26/21", Int64.Type}, {"03/05/21", Int64.Type}, {"03/12/21", Int64.Type}, {"03/19/21", Int64.Type}}),
    #"Unpivoted Other Columns" = Table.UnpivotOtherColumns(#"Changed Type", {"WBS/CTR", "PAAF"}, "Attribute", "Value"),
    #"Changed Type1" = Table.TransformColumnTypes(#"Unpivoted Other Columns",{{"Attribute", type date}, {"Value", type number}})
in
    #"Changed Type1"

And use the following formula to add column

YearMonth = FORMAT([Attribute],"YYYY-MM") 

Then you could calculate the sum and create visuals:

Measure =
CALCULATE (
    SUM ( 'Table'[Value] ),
    FILTER (
        'Table',
        'Table'[WBS/CTR] = MAX ( 'Table'[WBS/CTR] )
            && 'Table'[YearMonth] = MAX ( 'Table'[YearMonth] )
    )
)

The final output is shown below:

Please take a look at the pbix file here.

Best Regards,
Eyelyn Qin
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.