Aggregate values into calendar based upon start date and duration

jca · February 4, 2021, 8:10am

Hi,
I am struckling with creating sums of input values into a calendar.

I have data columns as follows;

Based upon start date and duration I would like to sum the staff need per month as shown below

I have tried various combinations of SUM and CALCULATE, but cannot get it right.

Sorry, i did not upload example, now done. I believe I am close, but i end up with having the sum of the two departments cyclic added up over the 12 months of first year. I cannot find out how to slice it by department and make run over the next years.
[AggregateDateSumExample.pbix|attachment]
DLNexample.xlsx (15.7 KB) (upload://qmCfoWRvYjq6NWot2mcdAd4JR8c.pbix) (74.9 KB)
AggregateDateSumExample.pbix (74.9 KB)

Shumaila · February 4, 2021, 10:28am

Hi Jca,
its really hard for members to provide a solution without looking at actual data, kindly provide your pbix file, so members can guide better.
Regards

EnterpriseDNA · February 4, 2021, 9:03pm

Hi @jca, we aim to consistently improve the topics being posted on the forum to help you in getting a strong solution faster. While waiting for a response, here are some tips so you can get the most out of the forum and other Enterprise DNA resources.

Use the forum search to discover if your query has been asked before by another member.
When posting a topic with formula make sure that it is correctly formatted to preformatted text </>.
Use the proper category that best describes your topic
Provide as much contextn to a question as possible.
Include the masked demo pbix file, images of the entire scenario you are dealing with, screenshot of the data model, details of how you want to visualize a result, and any other supporting links and details.

I also suggest that you check the forum guideline https://forum.enterprisedna.co/t/how-to-use-the-enterprise-dna-support-forum/3951. Not adhering to it may sometimes cause delay in getting an answer.

Please also check the How To Mask Sensitive Data thread for some tips on how to mask your pbix file.

BrianJ · February 5, 2021, 1:37am

@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]
    )
)