Hi Everyone ,
I have daily sales data from 2019 to 2021.
I want to do check for trend/pattern that how the sales is behaving in the first week of every month and last week of every month.
how I can convert that daily sales and also how to extract only 1st week of every month ?
Please suggest some ideas to do that.
For example data representation:
Date | Day | sales |
03-01-2019 | Friday | 10000 |
03-02-2019 | Saturday | 6000 |
03-03-2019 | Sunday | 5000 |
03-04-2019 | Monday | 8500 |
03-05-2019 | Tuesday | 9320 |
03-06-2019 | Wednesday | 4500 |
03-07-2019 | Thursday | 6888 |
Solved! Go to Solution.
Hi @ash25sumbre, Try using below formula to get week number which resets every month. I am sure this will help you to find 1st week of every month
1 + ToNumber(DateTimeFormat([date],"%W")) -
ToNumber(DateTimeFormat(DateTimeAdd([date], 1 - ToNumber(DateTimeFormat([date],"%d")),"days"),"%W"))
Hi @Rohitpatil7 ,
Thank you .
But then how I will group this every first week sales for different months ? please help me with this too
Hi @ash25sumbre,
Is this helpful?
Converted the date field to a workable date format using the datetime parse. Did a quick rename in the select and used my formatted date to get the first and last day of each month.
Fist of month = DateTimeTrim([Date],'firstofmonth')
Last of month = DateTimeTrim([Date],'lastofmonth')
To group first of the month and get sum of sales, you can add a summarize after to group by the first of month field and sum the sales field.
Please mark this as a solution if it has helped you solve your problem.
Thanks,
Carli