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
Solved! Go to Solution.
Hi @agrawaluk
So just to be clear, 24th of May would appear in two months? (April to May and May to June)
Cheers,
Hi @thableaus
Yes, that's right!
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
This is exactly what I was looking for. Thanks @marqueecrew
Apologies for previous misleading post.