Create a YTD Target

[JeanPierre]

Hi everyone, 

I have a table that defines targets of a company and this table has 3 columns: CompanyID, DepartementID, Target.

So I created a measure 'Target (monthly)' that is the anual target divided by 12 and 'Target (anual)' that corresponds to the table's 'Target' column.

Now I need to create a measure that is equal to the accumulated target by month, however the table of targets has no date so I cannot do TOTALYTD. I tried to do something like this " Target (YTD) = Target (monthly)* Month('Calendar' [Date])", to multiply the monthly target by the number of the filtered month.  

My problem is that DAX forms do not allow me to write this measure.

Can someone help me??

Thanks.

[fhill · Accepted solution]

The two tables below to the left are my raw data matching (I belive) your sample.  One table of CompanID, DepartmentID, & Annual Target).  My 2nd Table is a Linked By DepartmentID tables with Monthly Dates and Monthly Sales.

Once I created the join between DepartmentID I created the following Measures off the Detail Table:

SalesYTD = SUM(Table4[Sales])

Months = COUNTROWS(Table4)     *** You will probably need to do a conditional count here depending on department & Months, or hard code the value if you want to knock something out quick? ***

YTD_Target% = SUM(Table4[Sales]) / (SUM(Table3[Target])) 

Monthly Target = Table3[Target] / 12   ** Created on the Target Table **

+/- Goal = ( SUM(Table4[Sales]) / (SUM(Table3[Target]) / 12) ) - 1

To the right I have YTD Target % based on the SUM of Sales vs. Target by Department ID, and then a Monthly Detail by Date with Sales, Montly Target (Target / 12) and +/- Goal.  Lastly I quickly did a line graph showing how much each Month was UP or Down and did conditional formatting based on the values.

Hope this helps... 

FOrrest

[JeanPierre]

SOLVED!!

Thank you @fhill, this really resolved my problem.

It was very useful 😉

Thanks.

[MFelix]

Hi @JeanPierre,

Instead of making the Measure that has the Month target, why don't you change your querie in order to add one row per month with that split?

that way yoyu would have the information and could link to your calendar table.

Regards,

MFelix