Create Custom Column According last day of the month

[SSS]

Good Morning,

I need some  help trying to create a custom column that allows me to copy the values of one column when it is the last day of the month.

Here you can see how I have my data:

Date                Value        
01/01/2017       4
02/01/2017       1
02/01/2017       3
...
31/01/2017       4
31/01/2017       5
31/01/2017       6
01/02/2017       0
...
28/02/2017       5
...

And I want a custom column (it is mandatory to be a custom column, not a measure or a calculated one) to be able to give me this:

Date                Value            CustomColumn
01/01/2017       4                    null
02/01/2017       1                    null
02/01/2017       3                     ...
...
31/01/2017       4                     4
31/01/2017       5                     5
31/01/2017       6                     6
01/02/2017       0                     null
...
28/02/2017       5                    5
...

The only extra consideration to take into account is that if all the lasts days of a month (for example I have 5 records of 28/02) are 0 (in this case 5 rows that are 28/02 whose value is 0), then my last day will be the previous day that has values different to 0.

Thanks for you helps!!

[v-huizhn-msft]

Hi @SSS,

I test the Query statement @MarcelBeug posted using the following sample table, and get the expected result.

sample table
expected result
Please mark the right replay as answer, so more members will find workaround easily.

Best Regards,
Angelia

[rocky09]

CustomColumn= IF(Table2[Date ]=ENDOFMONTH(Table2[Date]),Table2[Value],BLANK())

[MarcelBeug · Accepted solution]

It's rather confusing what you mean by "custom column"; I interpreted it as a solution in Power Query (M).

So I created a query with a group by date for all records with Values <> 0, and I adjusted the code to group on mont-end dates.

Then I merged the result with the original source and the rest is pretty straightforward.

let
    Source = Table1,
    FilteredNotZero = Table.SelectRows(Source, each ([Value] <> 0)),
    MaxDates = Table.Group(FilteredNotZero, {"Date"}, {{"MaxDate", each List.Max([Date]), type date}}, null, 
                           (x,y) => Value.Compare(Date.EndOfMonth(x[Date]),Date.EndOfMonth(y[Date]))),
    Merged = Table.NestedJoin(Source,{"Date"},MaxDates,{"MaxDate"},"MaxDate",JoinKind.LeftOuter),
    Expanded = Table.ExpandTableColumn(Merged, "MaxDate", {"MaxDate"}),
    AddedCustom = Table.AddColumn(Expanded, "CustomColumn", each if [MaxDate] = null then null else [Value]),
    RemovedColumns = Table.RemoveColumns(AddedCustom,{"MaxDate"})
in
    RemovedColumns

Alternatively I could have added a custom column with month-end dates and group on that column, but the solution above is much more fun.

[SSS]

Thanks Guys!!