YTD monthly Target from annual target

[SimDam]

Hi,

I have a Target table that contains all the possible combination of Sales Office, Division, Product with Annual targets (the fiscal year is from July to June)

Sales Office	Division	Product	Target
Rome	1	Product A	100000
Rome	2	Product B	150000
Munich	1	Product A	200000
…

I have also a Calendar table going from 1-July-2020 to 30-Jun-2021 (fiscal year) which is currently not connected to the Target Table.

I would like to get the YTD Target on a monthly basis considering the monthly target is simply year target  / 12.

Thank you for your help

[ryan_mayu]

@SimDam 

please see the attachment. Hope this is helpful.

[SimDam]

Hi  @ryan_mayu,

Thank you for your reply.

If I understood correctly your solution, you have created a new table where you have a new calculated column with the YTD for each month and for each combination of the dimensions.

Actually, my target table is quite big (6 columns, 5 of which are dimensions) and more than 2000 rows (I have simplified the table for the post). So the new table will be 24000 rows which might be ok but my file is already quite slow because I have bigger tables to load as well.

Is there any solutions where I add one column to the target table with the last month of the fiscal year, I connect this new column to the Calendar table and then we have a Measure (not a calculated column) for the YTD Target?

Thank you for your support.

Best Regards,

[ryan_mayu]

@SimDam 

If you only want the YTD target, please see the attachment. Hope I didn't misunderstand your request.

[SimDam]

Hi @ryan_mayu,

First of all, let me thank you for the time you are spending on my request and also apologize if I wasn't clear enough.

Let me give you more details

Let’s have we have the below two tables

TARGET

Sales Office      Division Product Target

Rome   1          Product A          100000

Rome   2          Product B          150000

Munich 1          Product A          200000

ACTUAL

Sales Office      Division Product Actual   Month

Rome               1                      Product A          100       Jul-20

Rome               2                      Product B          200       Jul-20

Rome               1                      Product A          150       Aug-20

Munich             1                      Product A          100       Aug-20

Then I have 2 slicers (to simplify) one for the month and one for the Sales Office and I have a table by Product.

When I select July and all the sales offices, I see the below result. So in July I sold 100 for Product A and 200 for Product B and the YTD Target is 1/12 of the yearly target because July is the first month of the Fiscal year

Slicer Value: July-20

                                    YTD ACT          YTD TAR

Product A          100                               25000

Product B          200                               12500

If I select August now in the slicer and all the sales offices, I see the below result. So in July and August we sold total of 350 for Product A and 200 for Product B and the targets are now double of the July target because two months has passed.

Slicer Value: Aug-20

                                                YTD ACT          YTD TAR

            Product A          350                               50000

            Product B          200                               25000

If I now filter by Sales Office Rome keeping the Month as August, I will see the below result. So the YTD ACT and YTD TAR for Product A will change considering only the Rome office.

Slicer Value: Aug-20 – Sales Office Rome

                                    YTD ACT          YTD TAR

Product A          250                               16666.667

Product B          200                               25000

I hope this clarify my problem.

Let me know.

[ryan_mayu]

@SimDam 

please see the attachment. Hope I didn't make an easy thing be more complicated.

[SimDam]

Hi @ryan_mayu ,

It seems to work!

I have one last question: is it possible to have a solution where I don't have to expand the original target table to include all the months for each target? Ideally, I should not touch the target table (a part from adding one column with the last month of the fiscal year so I can connect it to the Calendar) and have everything in  a measure which maybe will compute the monthly target first and then the YTD Target. I read about Time intelligence functions but not sure how they can help in my case.

I hope my final question is clear.

Thanks.

[ryan_mayu · Accepted solution]

@SimDam 

this time i didn't touch the target table. please see the attachment. hope this is helpful.

[SimDam]

Hi @ryan_mayu ,

Sorry for my late reply.

Thank you for your solution. You have been simply amazing.

Thanks again!

[ryan_mayu]

@SimDam 

you are welcome.