Summarize within Dates Between

[mathew_528417]

Hey you all, I hope you all are ok!

I have the following (imaginary) database to work on:

What I need to achieve is below:

In other words, I need to multiplicate each demand due to its opening e closing date, considering the end of month of between those dates. But, the "AGE_DATE" can not be grater then the closing date. Also, if the demand does not has a closing date, the "AGE_DATE" must be the last day of refresh database.

Hope you can help me.

Thanks a lot. Regards from Brasil.

[tackytechtom · Accepted solution]

Hi @mathew_528417 ,

Here a solution in Power Query.

Before:

After:

Here the code in Power Query M that you can paste into the advanced editor (if you do not know, how to exactly do this, please check out this quick walkthrough)

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("TcrLCQAgDATRXvZsIFn/tYT034aiIB7nMe4wZiRQSdEsOl50MUMkR6ntQ3LH8T7mddunKN90ImIB", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Demand = _t, OPENING_DATE = _t, CLOSING_DATE = _t]),
    #"Changed Type 1" = Table.TransformColumnTypes(Source,{{"Demand", Int64.Type}, {"OPENING_DATE", type date}, {"CLOSING_DATE", type date}}),
    #"Added Custom 1" = Table.AddColumn(#"Changed Type 1", "AGE_DATE", each { Number.From ( [OPENING_DATE] ) .. Number.From ( if [CLOSING_DATE] = null then Date.From(DateTime.LocalNow())
 else [CLOSING_DATE] )  }),
    #"Expanded AGE_DATE" = Table.ExpandListColumn(#"Added Custom 1", "AGE_DATE"),
    #"Changed Type 2" = Table.TransformColumnTypes(#"Expanded AGE_DATE",{{"AGE_DATE", type date}}),
    #"Added Custom 2" = Table.AddColumn(#"Changed Type 2", "END_OF_MONTH", each Date.EndOfMonth([AGE_DATE])),
    #"Added Custom 3" = Table.AddColumn(#"Added Custom 2", "FILTER", each if [AGE_DATE] = [END_OF_MONTH] or [AGE_DATE] = Date.From(DateTime.LocalNow()) or [AGE_DATE] = [CLOSING_DATE] then 1 else 0),
    #"Filtered Rows" = Table.SelectRows(#"Added Custom 3", each ([FILTER] = 1)),
    #"Removed Columns" = Table.RemoveColumns(#"Filtered Rows",{"END_OF_MONTH", "FILTER"})
in
    #"Removed Columns"

Let me know if this helps 🙂

/Tom
https://www.tackytech.blog/
https://www.instagram.com/tackytechtom/

[mathew_528417]

Hey @tackytechtom , thanks a lot, man!

It worked better than I thought! 

Regards!

[tamerj1]

@mathew_528417 

Please refer to attached sample file