Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
RossP96
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

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
bpsearle
Resolver II
Resolver II

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??

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

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

 

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

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?

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

Hi Brian,

 

Sorry, now its showing the table on one as 1-feb-19, 1-may-19, 1-aug-19, 1-nov-19 for a quarter.

 

The problem is the actuals are only counting to them  dates and not the date range. i.e only showing as 1 on 1-feb-19 but wont show any for the 2nd etc.

 

thanks 

I’m not 100% sure I can follow what you mean. Do you have a link from the actuals table to the date table?

 

Can you provide a screen shot of what the problem is and describe what values you are expecting please?

 

Thanks, Brian

Hi Brian,

 

I have attached some screenshots to try and help. The first one shows the new calculated table you helped me to create, which has worked out the quarters perfectly. I've then shown the relationships from this new table to my date table, and from my actuals table (Instructions) to my date table. I've then shown the table I am trying to present and what it is in it.

 

The quarter budget part of the table works perfectly, the count of property ID (which is my actuals) doesnt. Becuase the dates of the quarters are single dates (1/11/19, 1/2/19 etc.), then the actuals are only picking up when the date is exactly them and not anything in between. 

 

For example on the table i have shown, there should be 2 in the first actual box, they have came from the 5th & 6th of November. But because they arent the first then they are not showing in the quarter.

 

I can't work out how to make it so the actuals count from the 1/11/19 all the way up until the end of Jan for Q1, instead of just looking at 1/11/19. 

 

I hope this makes sense

 

thanks 

 

 

 

SnipImage.JPGSnipImage.JPGSnipImage.JPGSnipImage.JPG

Hi @RossP96 sorry for not replying I've been crazy busy on a project.

Do you still need hep with this?

Thanks, Brian

Hi Brian,

 

No I managed to get it working!

 

Really appreciate the help I used most of what you said then just finished it off.

 

Thanks 

Thank you so much this has worked perfect!!

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.