community
cancel
Showing results for 
Search instead for 
Did you mean: 

Alteryx designer Discussions

Find answers, ask questions, and share expertise about Alteryx Designer.
SOLVED

Custom dates for a month

Meteor

Hi,

 

I am having two columns, one is date other is sales. I have to find monthly sales but a month is to be considered as 24th of last month to 25 of the present month.

For example: Sales for May would be from 24th April to 25th May. 

Anyone who could suggest me a formula/tool for this? Thanks in advance

 

Utkarsh

Alteryx Certified Partner
Alteryx Certified Partner

Hi @agrawaluk 

 

So just to be clear, 24th of May would appear in two months? (April to May and May to June)

 

Cheers,

Meteor

Hi @thableaus

 

Yes, that's right!

Highlighted
Alteryx Certified Partner
Alteryx Certified Partner

@agrawaluk,

 

I think that your dates are backwards.  The 25th of last month to the 24th of this month would avoid double counting the 24th.

 

Use the following formula to create a business month column as STRING (7):

 

IF ToNumber(Right([Date],2)) > 24 THEN
	DateTimeFormat(DateTimeAdd([Date],7,"days"),"%Y-%m")
ELSE
	DateTimeFormat([Date],"%Y-%m")
ENDIF

You'll get YYYY-MM as the output.  Now you can summarize by grouping on the business month.

 

Cheers,


Mark

Alteryx ACE & Top Community Contributor

Chaos reigns within. Repent, reflect and reboot. Order shall return.
Meteor

This is exactly what I was looking for. Thanks @marqueecrew 

Apologies for previous misleading post. 

Labels