Date time Analysis

[ash25sumbre]

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

[Rohitpatil7 · Accepted 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"))

[ash25sumbre · Accepted solution]

Hi @Rohitpatil7 ,

Thank you . 

But then how I will group this every  first week sales  for different months ? please help me with this too

[CarliE]

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

[ash25sumbre]

Thanks @CarliE for help.

Aishwarya