cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
Helper I
Helper I

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 

1 ACCEPTED SOLUTION

Accepted Solutions
Highlighted
Resolver II
Resolver II

Re: Setting 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

View solution in original post

14 REPLIES 14
Highlighted
Resolver II
Resolver II

Re: Setting Custom Dynamic Quarters

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

Highlighted
Resolver II
Resolver II

Re: Setting Custom Dynamic Quarters

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.

Highlighted
Helper I
Helper I

Re: Setting Custom Dynamic Quarters

I'm fairly new to calculated tables so not too sure where to start with creating the 4 date ranges??

Highlighted
Resolver II
Resolver II

Re: Setting Custom Dynamic Quarters

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

Highlighted
Helper I
Helper I

Re: Setting Custom Dynamic Quarters

Hi Brian,

 

Thanks for the help, I think I'm close but when i get to multiplying by 3 it ends up being 3 years in the future?

 

Capture.JPG

 
Highlighted
Resolver II
Resolver II

Re: Setting 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

View solution in original post

Highlighted
Helper I
Helper I

Re: Setting Custom Dynamic Quarters

Thank you so much this has worked perfect!!

Highlighted
Helper I
Helper I

Re: Setting Custom Dynamic Quarters

Ahh only problem now is that the actuals aren't correspoinding to the correct dates in a matrix by quarters.

 

I've used the quarter name from this calculated table and budget, then the actual from elsewhere.

 

Do I need to somehow link the quarters to my date table and use the quarters from there instead?

Highlighted
Resolver II
Resolver II

Re: Setting Custom Dynamic Quarters

Hey

Yes, both the actuals table and the new budget quarter table need to link to a date table. The budget quarter table needs to link on the calculated date quarter column that you created.

In visuals you can use the date table and select the quarter from the hierarchy.

Thanks, Brian

Helpful resources

Announcements
Community Conference

Power Platform Community Conference

Check out the on demand sessions that are available now!

Community Conference

Microsoft Power Platform Communities

Check out the Winners!

secondImage

Power Platform 2020 release wave 2 plan

Features releasing from October 2020 through March 2021

Tech Marathon

Maratón de Soluciones de Negocio Microsoft

Una semana de contenido con +100 sesiones educativas, consultorios, +10 workshops Premium, Hackaton, EXPO, Networking Hall y mucho más!

Top Solution Authors
Top Kudoed Authors