How to arrive date which is before last date

[bicoffee]

I want two dates in new columns  as last date and date before last date  as previous day.

IN the below scenarion last date is 26 may 2021 and before that data is present for 12 May 2021. So previous date has to show last but one date and quantity

DATE, CATEGORY, ITEM, QUANTITY, UNIT, Last stock date, PREVIOUS DAY,QUANTITY
26 May 2021, 123, 456, 10.00, NOS, 26 May 2021, 12 May 2021,15

[Anonymous]

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

[Anonymous · Accepted solution]

@bicoffee 

Change the first 2 measures to the following dax with allselected and item column included.

last date = CALCULATE(MAX([Date]),FILTER(ALLSELECTED(Sheet2),[Store]=MAX([Store])),FILTER(ALLSELECTED(Sheet2),[ ITEM]=MAX([ ITEM])))

date before last = CALCULATE(MAX([Date]),FILTER(ALLSELECTED(Sheet2), [Date]<[last date]))

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

[Anonymous]

@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.

[bicoffee]

Hi, 

Thanks for the solution. It works if i am selecting any one item. but when i select all items it is not giving proper result. I am just checking for the issue.

[Anonymous]

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]

Hi,

Date has to be filtered based on the store and the item.

I have data in one table . it is getting updated on daily basis. For some cases for some of the stores data will not update on daily basis. SO i want to know when is the last data i have for the particular store and also before that which date i have the data, For example for one store i have data till 26 May. Before that data receied on 12th May. So i wan to know for each day when is the last date and before that when i have received the data.

find below sample data of table

DATE STORE ITEM QUANTITY UNIT
26 May 2021 123 456 10.00 NOS
12 May 2021 123 456 15.00 NOS
11 May 2021 123 456 15.00 NOS
07 May 2021 123 456 15.00 NOS
06 May 2021 123 456 15.00 NOS
05 May 2021 123 456 15.00 NOS
04 May 2021 123 456 15.00 NOS
03 May 2021 123 456 15.00 NOS
28 April 2021 123 456 15.00 NOS
27 April 2021 123 456 15.00 NOS
26 April 2021 123 456 15.00 NOS
24 April 2021 123 456 15.00 NOS

[SushainKoul]

Hi @bicoffee ,

Try this calculated column

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

Hope this helps.

[bicoffee]

Hi,

Data last date is 26 May2021 and before that data available is 12 May 2021.  So from dax i should get date as 12 May2021 not 25May2021. Currently from your formula i am getting 25May2021

[SushainKoul]

Hi @bicoffee ,

Can u plz explain the scenario once again because I am not able to understand your format if my solution doesnt meet your expectations?

[bicoffee]

HI,

I have data in one table . it is getting updated on daily basis. For some cases for some of the stores data will not update on daily basis. SO i want to know when is the last data i have for the particular store and also before that which date i have the data, For example for one store i have data till 26 May. Before that data receied on 12th May. So i wan to know for each day when is the last date and before that when i have received the data.

[bicoffee]

Hi,

find below sample data of table

DATE CATEGORY ITEM QUANTITY UNIT
26 May 2021 123 456 10.00 NOS
12 May 2021 123 456 15.00 NOS
11 May 2021 123 456 15.00 NOS
07 May 2021 123 456 15.00 NOS
06 May 2021 123 456 15.00 NOS
05 May 2021 123 456 15.00 NOS
04 May 2021 123 456 15.00 NOS
03 May 2021 123 456 15.00 NOS
28 April 2021 123 456 15.00 NOS
27 April 2021 123 456 15.00 NOS
26 April 2021 123 456 15.00 NOS
24 April 2021 123 456 15.00 NOS

[SushainKoul]

Hi @bicoffee ,

Now from the data set i got the picture to some extent. Correct me if I am wrong. So from the last date of any month u want to know what is the previous next date where the particular store is achieved. Right?

[bicoffee]

Hi,

Yes, If last date is 26th May for one store and for same store 12th May is the before last date.

[bicoffee]

HI,

Could you find out any solution ?

[SushainKoul]

Hi @bicoffee ,

Give me 1-2 days. I will tell u. I got caught up with my some work. I have started looking on it. In the meantime u can prceed with your other work and once I am done I will drop u a message. Hope that works for u.

[bicoffee]

Hi,

Sounds good...Thanks

[SushainKoul]

Your welcome. When I have any latest update I'll drop u a text.

[SushainKoul]

Yaa I got it. I got better idea from the dataset u gave. Give me sometime. I will get back to u soon about this.