How to filter previous months (in a year) data from datatable (Dynamically)?

Help Studio
, , , , , , ,
1

Hi Everyone,
I am stuck with a problem , I need to filter previous months data from a datatable (Dynamically).

excel should have all data that is prior to the same month.

eg: if you run on 1st March 2022 – all data till 28 Feb 2022 would be there.
eg: if you run on 28th Feb 2022 – all data till 31st Jan 2022 would be there.

Sample screenshot attached below -

Capture
Capture1137×198 28 KB

since current month is Feb. => I required prior month data (i.e. , Jan as highlighted in above screenshot).
I’ve tried different ways ; If anyone can share any insights , ideas or LINQ queries it would be helpful.

Thanks
Happy Automation !

2

@rameespk23 ,

Below Linq Query can be used to filter for previous month dates:

in assign activity :
dt1=
dt1.AsEnumerable.Where(function(row) Cdate(row(“Date”).ToString) >=date1 and C
date(row(“Date”).ToString) <= date2).CopyToDataTable

where dt1 → input datatable

date1 → New DateTime(Now.Year,Now.Month,1).AddMonths(-1) → previous month first day date

date2 → New datetime(now.Date.Year, now.Date.Month,1).AddDays(-1) → previous month last day date.

In this way, previous month date values can be filtered.
Hope this helps.

3

Hi @rameespk23

Try this below expression in Filter data Table

DateTime.ParseExact(Row("Date").ToString,("dd-MM-yyyy"),Globalization.CultureInfo.InvariantCulture).AddMonths(-1).ToString("dd-MM-yyyy")

image

Regards
Gokul

4

Hey @rameespk23 !! Try this!!

(From r in dt_test.Select()
	Where Datetime.ParseExact(r("Date").toString,“dd-MM-yyyy”,System.Globalization.CultureInfo.InvariantCulture).Month = DateCheck.AddMonths(-1).Month
	Select r).CopyToDatatable

image
image1920×1021 80.9 KB

Hope it helps!

1 Like
6

Hey @Ramya_K ! I accidentally tagged you, I was supposed to tag the author! Sorry!

7

@Ramya_K Thanks for response . The requirement was to get data for previous months

If I am running bot in MAY , datatable should contain have data from JAN,FEB,MAR,APRIL . i.e ; data till current month.

9

@rameespk23 you could use filter data table (or linq) to filter the data column >= 01.01.actual year and < first day of the actual month. Only the data for the previous months would remain.

10

@rameespk23, then we can just update the date1 variable to

date1 → New DateTime(Now.Year,1,1)→ current year first date

Hope this helps.

11

Hey @rameespk23 ! Can you try this, please?

(From r in dt_test.Select()
	Where Datetime.ParseExact(r("Date").toString,“dd-MM-yyyy”,System.Globalization.CultureInfo.InvariantCulture).Month < Datetime.Now.Month
	Select r).CopyToDatatable
12

Thanks brother ! This worked as expected !!! :star_struck:

1 Like
13

Thanks @Ramya_K @PeCour @Gokul001 @gabrielribas4 for your responses ! :innocent:

14

This topic was automatically closed 3 days after the last reply. New replies are no longer allowed.