Forum Discussion
Setting Custom Dynamic Quarters
Hi,
I currently have a target database for different agencies. Each agent has a £ target, a starting date & an ending date (always 12 months).
I need to be able to output a table (or something else) that shows the actuals v targets by quarter. So it would just be the total target divided by 4, and then the actuals for each time period. I would only have 1 agent selected at a time.
The problem being that each agent may have a different start date, so the quarters would be different for each of them. I'm not sure how to create a measure to divide the target in to 4 and then show the actuals relating to that quarter?
The database is just 1 row per agent, with a column for start date, a column for end date, and a column for target.
Any ideas?
Thanks
The last part of the EDATE is the number of months to adjust by and we want values 0, 3, 6 and 9.
[Quarter budget] * 3 should be TableName[Value] * 3
PS it helps readability / understanding to qualify column names with their tablename first.
Thanks, Brian
14 Replies
First off, I don’t believe this is too difficult, that said I might have misunderstood what you asked!
You will have a date table with continuous dates between your start and end dates that you want to report over. As this contains dates, your target start dates and actual dates will all link to the date table. You can then use the date table to have any hierarchy you want to define e.g. Year, Half, Quarter, Date or Year, Quarter or just Quarter as you mentioned.
Hope this helps, any questions just shout
Apologies, I was a bit too quick to answer. It is a little bit more work than I first thought.
For the budget, you will need to expand that out into a budget for each quarter. I prefer to do things with data structures to make DAX calculations easier. I suggest creating a calculated table where for each individual target budget it creates 4 rows with appropriate dates and values for each quarter.
Then when the calculated budget table is used with the actuals table the values will be shown as expected in each quarter on the date table.
I'm fairly new to calculated tables so not too sure where to start with creating the 4 date ranges??
