check if data is for previous month

arkiboys 9,686 Reputation points
2022-11-10T13:05:50.17+00:00

hello,
in data flow I would like to make sure the data is for previous month only
my concern is what if we are in jan 2023, will this filter for Dec 2022 as well?
This is what I am doing in two separate filters

last month data:
step1--> filter in dataflow for year
equals(year(toDate(dateColumnName,'yyyyMMdd')),year(currentUTC()))

step2--> filter in dataflow for last month
equals(month(toDate(dateColumnName,'yyyyMMdd')), month(addMonths(toDate($p_CurrentDate, 'yyyy-MM-dd'),-1)))

thank you

Azure Data Factory
Azure Data Factory
An Azure service for ingesting, preparing, and transforming data at scale.
10,113 questions
0 comments No comments
{count} votes

Accepted answer
  1. KranthiPakala-MSFT 46,437 Reputation points Microsoft Employee
    2022-11-11T02:13:34.537+00:00

    Hello @arkiboys ,

    Thanks for the question and using MS Q&A platform.

    If check or make sure if the date column is from previous month or not you can use below expression in dataflow expression builder.

    equals(subMonths(lastDayOfMonth(currentDate()),1), lastDayOfMonth(toDate(dateColumnName,'yyyy-MM-dd')))  
    

    You can use combination of lastDayOfMonth() and subMonths() functions to achieve your requirement and this will calculate the correct Month difference even there is a change in year same as yours (for Example Current month is Jan 2023 and the dateColumn Month is Dec 2022). These functions will take care of it automatically. You can avoid the extra step to verify the year difference.

    Sample demonstration below:

    259293-image.png

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

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

    • Please don't forget to click on 130616-image.png or upvote 130671-image.png 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

0 additional answers

Sort by: Most helpful