I'm looking at this, and I need some more clarity.
You have a Start Date and End Date parameter. From your description it looks like you expect the user to select some portion of a month. (First of the month through 9th of the month, for example, as the sample workbook is initially uploaded here.)
And then based on [N Periods Back], you want to show N-many historical months, summing up only data from the first through the 9th of those months.
My supposition is that you will be showing sums of measures within the stretch of days, on a month by month basis.
But your Start and End parameters will let me cross a month boundary. Or multiple months. If I select June 1 through August 9, and then I select [N Periods Back] to be 3, what do you expect to happen?
If your user selects May 20 through May 31, and then selects N = 4, what would you want the logic to do for February, given that February doesn't have 31 days?
These are some initial questions I have.
Luciano Vasconcelos Thanks a ton for your help and support !!
Joe Oppelt Thank you very much for your reply Joe.
Your understanding of requirement is spot on , i want to display the measures based on a portion of month with a flexibility for the users to select as many months backwards as they would like.
First Scenario you have raised :
As the scenario you mentioned that start date and end date parameters cross the boundary of month, you are right , i wasn't thinking of this Scenario's and its now clear to me that its a flaw.
Also, the Second scenario about going 4 periods back from may to february, my thinking was, it should display only until 28th of February but as you have pointed correctly, this wouldn't work either.
I am a newbie to Taebleau and tried to come up with the logic to get the required result following some examples i have found. .
My only objective was to get i want to display the measures based on a portion of month with a flexibility for the users to select as many months backwards as they would like. ( is it possible to achieve when may be the user could select just one date parameter than two ? )
Could you please help me out with any alternative approach possible in order to get the result ?
Much appreciate your help !!
In Sheet 4 I built a [Get Days] filter. This will limit the rows to the day range the user selects.
In Sheet 5 I built two calcs and displayed them for testing purposes. I built a start date calc and an end date calc. they determine the first day of the beginning of the month range based on the user's selections for start date, and the last day of the end month the user selects.
In Sheet 6 I created a calc to get the full date range and put that on the filter shelf with the [Get Days] filter.
[Get Months] grabs the full range of rows from the first day of the beginning month to the last day of the end month, and [Get Days] grabs only the smaller range of days the user selects.