Custom dates for a month

[agrawaluk]

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

[Thableaus]

Hi @agrawaluk 

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

Cheers,

[agrawaluk]

Hi @thableaus

Yes, that's right!

[MarqueeCrew · Accepted solution]

@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

[agrawaluk]

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

Apologies for previous misleading post.