CODEWITHSUNDEEP
azure-data-factory
newbie
newbie

Reputation: 53

Filtering on Date column in ADF Dataflow

Problem

I want to be able to filter on the latest date in the data set. Which would normally be current month -1 as the dataset is for last month. For example, if we are in January we would be working with December 2021 dates. If we are in February 2022 we will be working with January 2022 dates.

I am using ADF dataflow for transformations and my data somewhat looks like this:

ID Customer Month Year
1 ABC December 2021
1 ABC November 2021
2 XYZ December 2021

Output that I want

ID Customer Month Year
1 ABC December 2021
2 XYZ December 2021

What I have tried

I have tried a few things, like passing Parameters and using Filter mapping. However none of the methods are working.

Here's the expression I used for Filter

{Month Year}==addMonths(currentDate(), -1, 'MMMM YYYY')

Upvotes: 0

Views: 3259

Answers (1)

NiharikaMoola
NiharikaMoola

Reputation: 5074

I have repro’d in my lab and the below expression worked for me.

Source:

enter image description here

Filter transformation:

month(addMonths(currentDate(),-1)) == month(toDate({Month Year},'MMMMM yyyy'))

enter image description here

Filter output:

enter image description here

Upvotes: 2

Related Questions