Start of month offset

[MichaelL]

Hello,

I would like to "shift" the start of the month date for the whole model.

Normally, the monthly calculations start on the first day of the month, so all breakdowns or drills follow a monthly, quarterly and yearly basis with starting the day on the 01 of each month.

Due to the different "philosophy", the data, in my case, should be presented from the 21st day of the previous month to the 21st day of the current month.

I have tried to apply a filter to highlight only one month, but it does not work well for the series of months.

Is there any way of "shifting the data aggregation and drilling down the way that it will always show the month as 21st-21st instead of 1st-30th?

[dazfoz]

In your date table, can you simply add additional columns for your reporting period eg 21st April to 20th May is (April or May depending how you record the months), same logic for Quarters and Years.

I have a financial calendar that I have my April-March accounting period aligned with the dates in your calendar, this way you can use the Accounting Week/Period/Quarter/Year to slice the data.

Hope this simple answer helps, Im a bit of a Newbie so I may be way off the mark!

Daz

[MichaelL]

@dazfoz Thank you for your input.

Could you share more details?

I have tried to replicate your way, but it seems to be masive "manual" work as there is large amount of the data.

Michael

[Ajendra]

Hello,

Kindly Share demo data along with the desired output.

Thanks!

[MichaelL]

And the expected outcome should be grouped on the basis of the dates using 21/01/2023 till 21/02/2023, 21/02/2023 till 21/03/2023 and so on. The values are changing compared to grouping based on 01/01/2023 till 31/01/2023....

[MichaelL]

Hi.

Here is a part of the table with data which is much longer as the data is collected over the years.

Name of Applicant	Date	assigned text	Number (unit)
John Weber	02/01/2023	Revenue	3
John Weber	02/01/2023	Sales	0.5
John Weber	02/01/2023	Revenue	0.5
John Weber	02/01/2023	Revenue	1
John Weber	02/01/2023	Revenue	0.5
John Weber	02/01/2023	Revenue	2
John Weber	02/01/2023	Sales	0.5
John Weber	02/01/2023	Sales	0.5
John Weber	03/01/2023	Sales	0.5
John Weber	03/01/2023	Sales	0.5
John Weber	03/01/2023	Sales	0.5
John Weber	03/01/2023	Sales	0.5
John Weber	03/01/2023	Sales	0.5
John Weber	03/01/2023	Sales	0.5
John Weber	03/01/2023	Sales	0.5
John Weber	03/01/2023	Sales	0.5
John Weber	03/01/2023	Sales	0.5
John Weber	03/01/2023	Revenue	1
John Weber	03/01/2023	Revenue	1
John Weber	03/01/2023	Revenue	0.5
John Weber	04/01/2023	Sales	0.5
John Weber	04/01/2023	Sales	0.5
John Weber	04/01/2023	Sales	0.5
John Weber	04/01/2023	Revenue	1
John Weber	04/01/2023	Revenue	1.5
John Weber	04/01/2023	Sales	0.5
John Weber	04/01/2023	Sales	0.5
John Weber	04/01/2023	Sales	0.5
John Weber	04/01/2023	Sales	0.5
John Weber	04/01/2023	Sales	0.5
John Weber	04/01/2023	Revenue	0.5
John Weber	05/01/2023	Revenue	0.5
John Weber	05/01/2023	Sales	0.5
John Weber	05/01/2023	Sales	0.5
John Weber	05/01/2023	Revenue	0.5
John Weber	05/01/2023	Sales	0.5
John Weber	05/01/2023	Sales	0.5
John Weber	05/01/2023	Sales	0.5
John Weber	05/01/2023	Sales	0.5
John Weber	05/01/2023	Revenue	1
John Weber	05/01/2023	Sales	0.5
John Weber	05/01/2023	Sales	0.5
John Weber	05/01/2023	Revenue	0.5

The visualisation looks like that with the drill down on the dates to monthly overview based on typical calendar dates:

The data relationship is built with the dates table:

And the date table has been created using the "Table = CALENDARAUTO()" command.

Let me know if I missed anything useful.

BTW, I couldn't upload the model or table in Excel format.