How to arrive date which is before last date

Hi bicoffee ,

Try this calculated column

=CALCULATE(MAX(Data[Date_Time]),FILTER(Data,Data[Date_Time]<EARLIER(Data[Date_Time])))

Hope this helps.

I am guessing that this is a csv file, with the DATE column. You can probably just Use the Group function in the GUI and group by DATE and use All Rows as the aggregation. Let's call that step "Grouped",  and name the All Rows column "Details". Then you can do:

Table.AddColumn(Grouped, "SingleDates", each [Details]{0})

This gives you a new table column, each table containing the first row of the grouped table. Then after removing all but the last (newest) column, expand the table column.

--Nate

bicoffee 

Check out my example, you need 4 measures. Pbix attached.

last date = CALCULATE(MAX([Date]),FILTER(ALLSELECTED(Sheet2),[Store]=MAX([Store])))
Second last date = CALCULATE(MAX([Date]),FILTER(ALL(Sheet2), [Date]<[last date]))

last date quantity = IF(MAX([Date])= [last date],MAX([ QUANTITY]))
second last date quantity = IF(MAX([Date])=[date before last],MAX([ QUANTITY]))

Paul Zheng _ Community Support Team
If this post helps, please Accept it as the solution to help the other members find it more quickly.

Not sure if you are looking for a solution in M, but if so, you can group on Categoey and Item, and include an "All Rows" column to your grouping, let's say you name that column "Details". Then you can reduce the nested table rows to the latest two dates like:

Table.AddColumn(GroupStepName, "FilteredTables", each Table.MaxN(Table.Distinct([Details], "DATE"), "DATE", 2))

Then remove all but this new table column, then expand the column.  That should do it!

--Nate