staffing levels cumulative data

Hi AndrewPF , To get the desired results you need to unpivot your data from columns to rows .

Sample data:

PQ:

let
    Source = base,

    Unpivoted = Table.UnpivotOtherColumns(
        Source,
        {"Emp ID", "Department", "Role", "Location"},
        "MonthNum",    
        "FTE"
    ),

    CleanedMonth = Table.ReplaceValue(
        Unpivoted,
        "FTE Month ", "",
        Replacer.ReplaceText,
        {"MonthNum"}
    ),

    MonthAsInt = Table.TransformColumnTypes(
        CleanedMonth,
        {{"MonthNum", Int64.Type}}
    ),

  
    CalendarMonth = Table.AddColumn(
        MonthAsInt,
        "Calendar Month",
        each Date.AddMonths(#date(2026, 3, 1), [MonthNum] - 1),
        type date
    ),

    FilteredZeros = Table.SelectRows(
        CalendarMonth,
        each [FTE] <> 0
    ),

    Reordered = Table.ReorderColumns(
        FilteredZeros,
        {"Emp ID", "Department", "Role", "Location",
         "MonthNum", "Calendar Month", "FTE"}
    )

in
    Reordered

OP:

PBIX:
FTE Calculation.pbix

Thanks
If you found this helpful, please consider giving it a kudo and marking it as the accepted solution — it goes a long way in helping others facing the same issue.

For more Power BI tips and discussions, let’s connect on LinkedIn:
https://www.linkedin.com/in/natarajan-manivasagan

Cheers!

The standard approach in Power BI is to unpivot the data to bring it into usable format

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WSs0tUPB0UdJRcgtxVfDNzyvJUDAwROUaoXKNUbkmqFxTVK4ZKtcclWuByrVE4RoaoHJRXWVopBSrE60EdqsBFJuQSIANMIJqNjSgjAQbBgobUzhhQBQGazShgguQ3GFKTU+ZUdMwUAowN8VH4AklCyQh8twBNgfKJIhiYwE=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Column1 = _t, Column2 = _t, Column3 = _t, Column4 = _t, Column5 = _t, Column6 = _t, Column7 = _t, Column8 = _t, Column9 = _t, Column10 = _t, Column11 = _t, Column12 = _t, Column13 = _t]),
    #"Promoted Headers" = Table.PromoteHeaders(Source, [PromoteAllScalars=true]),
    #"Unpivoted Other Columns" = Table.UnpivotOtherColumns(#"Promoted Headers", {"emp ID"}, "Attribute", "Value"),
    #"Changed Type" = Table.TransformColumnTypes(#"Unpivoted Other Columns",{{"Value", Int64.Type}})
in
    #"Changed Type"

How to use this code: Create a new Blank Query. Click on "Advanced Editor". Replace the code in the window with the code provided here. Click "Done". Once you examined the code, replace the entire Source step with your own source.

From there the visual writes itself.