Aggregate values into calendar based upon start date and duration

DAX DAX Calculations
4

@jca,

Not surprised the DAX is giving you problems. I think if you go down the road of a DAX-only solution, the code is quite complex. (Even halfway around the world, I think I hear @AntrikshSharma saying "Challenge Accepted!). However, if you transform the data as follows first in Power Query, the DAX simplifies down to one short, straightforward measure.

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("PczBDcAgDAPAXfwmyEkplFmi7L9GgSp9+OOT7Q6qsInRFAVjRVkHojj0FmXK3FLngT2xDbbKvsL6/HDloh34rozCnmAHOiJe", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"Start Date" = _t, DurationMonths = _t, StaffPerMonth = _t]),
    #"Split Column by Delimiter" = Table.SplitColumn(Source, "Start Date", Splitter.SplitTextByDelimiter("-", QuoteStyle.Csv), {"Start Date.1", "Start Date.2", "Start Date.3"}),
    #"Reordered Columns" = Table.ReorderColumns(#"Split Column by Delimiter",{"Start Date.2", "Start Date.1", "Start Date.3", "DurationMonths", "StaffPerMonth"}),
    #"Merged Columns" = Table.CombineColumns(#"Reordered Columns",{"Start Date.2", "Start Date.1", "Start Date.3"},Combiner.CombineTextByDelimiter("/", QuoteStyle.None),"Start Date"),
    #"Changed Type" = Table.TransformColumnTypes(#"Merged Columns",{{"Start Date", type date}, {"DurationMonths", Int64.Type}, {"StaffPerMonth", type number}}),
    #"Added Custom" = Table.AddColumn(#"Changed Type", "End Date", each Date.AddMonths( [Start Date], [DurationMonths] )),
    #"Added Custom1" = Table.AddColumn(#"Added Custom", "Duration Days", each [End Date] - [Start Date]),
    #"Changed Type1" = Table.TransformColumnTypes(#"Added Custom1",{{"Duration Days", Int64.Type}}),
    #"Added Custom2" = Table.AddColumn(#"Changed Type1", "Dates Between", each List.Dates( [Start Date], [#"Duration Days"], #duration( 1, 1, 0, 0))),
    #"Expanded Dates Between" = Table.ExpandListColumn(#"Added Custom2", "Dates Between"),
    #"Inserted Year" = Table.AddColumn(#"Expanded Dates Between", "Year", each Date.Year([Dates Between]), Int64.Type),
    #"Inserted Month" = Table.AddColumn(#"Inserted Year", "Month", each Date.Month([Dates Between]), Int64.Type),
    #"Removed Other Columns" = Table.SelectColumns(#"Inserted Month",{"StaffPerMonth", "Year", "Month"}),
    #"Removed Duplicates" = Table.Distinct(#"Removed Other Columns"),
    #"Added Custom3" = Table.AddColumn(#"Removed Duplicates", "MoInYr", each Text.From( [Year] ) & Text.PadStart( Text.From( [Month] ), 2, "0" ) & "00"),
    #"Changed Type3" = Table.TransformColumnTypes(#"Added Custom3",{{"MoInYr", Int64.Type}})
in
    #"Changed Type3" 

Total Staff = 

CALCULATE(
    SUM( Data[StaffPerMonth]),
    TREATAS(
        DISTINCT( Dates[MonthnYear] ),
        Data[MoInYr]
    )
)

image

The approach I took in Power Query is a variant of the solution that @haroonali1000 presented for Problem of the Week #4.

I hope this is helpful. Full solution file attached below

– Brian
eDNA forum – Allocating Monthly Staff Solution.pbix (176.0 KB)

PS – welcome to the forum, great to have you here!

3 Likes