Forum Discussion
Helper ISetting Custom Dynamic Quarters
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
Resolver IIFirst 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
- bpsearle
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.
RossP96I'm fairly new to calculated tables so not too sure where to start with creating the 4 date ranges??
- bpsearle
OK hands up, this took a while to figure out!
The first thing is to create a new calculated table based on your existing budget master table but that has an additional 4 rows for each row in the budget table. These 4 rows will represent the 4 quarters.
Budget Quarters = CROSSJOIN('Budget Master',GENERATESERIES(0,3,1))
A new column will be added called Value that contains 0 to 3.
We then add a new calculated column for Budget Quarter Value and that is your master budget divided by 4.
Then add another calculated column for the budget quarter date and that is EDATE(budget master date, the Value column mentioned above * 3). This will give you a date 3 months on from your budget date.
Hide the unwanted columns. Equally if the main master budget table has loads and loads of columns that you don't need for the budget quarter table just use a SUMMARIZE and choose the columns you want. This would go in the CROSSJOIN.
If this is difficult to follow let me know
Thanks, Brian