Get Last month in Every Quarter in the year

Question

Hi ,

In ADF How to get Last Month in every Quarter for current year

Thanks in Advance

Answer 1

Hi @Raj0125 ,
Thankyou for using Microsoft Q&A platform and posting your query.

If I understand your query correctly, you want to dynamically fetch the last month of every quarter using ADF pipeline . Please correct me if my understanding is wrong.

You can go for any of the following approaches according to your requirement:

Option1:
As the last month of every quarter is a fixed value, so you can create a variable which stores these month's name i.e. March, June, September and December. Then use the variable to carry out the action you want to perform on top of that.

Option2:

For this requirement, you can create a dataflow where source transformation is pointing to a table having monthNumber and monthName . We can use derivedColumn transformation to add a new column LastMonth and check whether the Month is the last month of the quarter or not by using the expression iif((mod(MonthNo,3)==0),'Last Month','Not the last month')

Here is how the output looks like:

Option3:

Use conditional split transformation in data flow to separate the last months of quarters from non-last months using the expression equals(mod(MonthNo,3),0) and load them into separate tables

Option4:
You can use LookUp activity pointing the dataset to Azure SQL table and use SQL query to get the last month of each quarters:
Select *,case when Monthno between 1 and 3 then 3 when Monthno between 4 and 6 then 6 when Monthno between 7 and 9 then 9 when Monthno between 9 and 12 then 12 end as lastmonthoflastquarter from MonthList

Hope this will help. Please let us know if any further queries.

------------------------------

• Please don't forget to click on or upvote button whenever the information provided helps you.
  Original posters help the community find answers faster by identifying the correct answer. Here is how
• Want a reminder to come back and check responses? Here is how to subscribe to a notification
• If you are interested in joining the VM program and help shape the future of Q&A: Here is how you can be part of Q&A Volunteer Moderators