Measure With Beginning YTD Value even if No Fact Row

[Derekp978]

All -  I have a sales dashboard that is based off a contract fact table - linked to that Fact table is a Date dimension and a Salesperson Dimension.  I need two calculations - one of which should come directly from the first, but need not as long as I can fill in the value.

Each contract has an effective date and a contract amount - I need to create the following:

1. A YTD Contract Amount by Salesperson
2. A Measure reflecting the Total Expected Quota they should have at that time of the year (A KPI will be created as a % of Actual / Goal)

My issue is that for each salesperson they do not always sign or close a contract in a given month.  Let's say their 2021 Annual Quota was $12,000 - meaning for each month they should have $1K as a goal - by July this person should have a Goal of $7K, if the actual is $14K then they are at 200%.

For Salespersons that have monthly closed contracts this works fine - or even ones that have at least one record in 2021 as YTD carries forward.  BUT if a Salesperson does not have a closed contract in Jan or Feb then the Actual is Blank (instead of $0 and the Goal is also blank, because the numerator is missing)

Is there any way to force a $0 record to show in the months YTD that have yet to see experience?  See the Yellow box in the picture below - the reason I need them to count, other than continuity - is I also need their Goal to be part of the Aggregate - For January there should be 2 SalesPersons with $150 K goal each, meaning the Total Goal should be $300K, but instead it only adds to 150K.

Note: I don't want a show all option, as the dates for the quota would be very specific to Hire Date and Term dates, so there are times when a blank is appropriate and I will work that into the final answers. Also there are other account owners that I do not want showing up because they have no contract value associated.  The Measure + $0 and Show Blank Values options display any account owner regardless of certain criteria.  For Hired Employeed that should have value I want a $0.

Thanks ahead of time, 

Derek

[amitchandak]

@Derekp978 ,Can you share sample data and sample output in table format? Or a sample pbix after removing sensitive data.

How you created YTD, have you used date table and time intelligence 

and meausre like

YTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESYTD('Date'[Date],"12/31"))
Last YTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESYTD(dateadd('Date'[Date],-1,Year),"12/31"))

if needed add +0

YTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESYTD('Date'[Date],"12/31")) +0

[Derekp978]

Hi @amitchandak  - Here is my YTD formula - I have an effective date (day contract is effective and the quota dollars apply) on the fact table that links to the "effective date" table, marked as a date -

Total Quota Attained YTD:= 
VAR enddate = TODAY()
VAR currentdate = FILTER( VALUES('Effective Date'[Effective Date]) , 'Effective Date'[Effective Date] <= enddate)

VAR Result =  CALCULATE([Total Quota Attainment], DATESYTD(currentdate), NOT(ISBLANK('Opportunity Owner'[Hired Date])))

Return Result 

  The problem with the +0 is that it removes all the explicit filters (like the future dates YTD) that I don't want - my date table has entries in the future - but I don't want YTD to extend beyond TODAY().  When I add + 0 I get values going through end of year and I also get Contract Owners that I don't want (right now filtered by the Not Blank Hired Date (I will change that later, but works for now).   However the Jan/Feb dates highlighted in the original DO give $0s, I want to stop the project of dates into the future, the + 0 seems to just give a generic cross apply to everything.

[amitchandak · Accepted solution]

@Derekp978 , first you should use a date table, like last example I shared. If that do work try this with date tbale

VAR enddate = TODAY()
var _min = calculate(Min('Calendar'[Date]), datesytd(Date[Date]) )
var _max = today()
Measure = var _1= CALCULATE([Total Quota Attainment], datesytd(Date[Date])) +0
return
CALCULATE(if(max('Calendar'[Date]) <_min || max('Calendar'[Date]) >_max , BLANK(), _1))

[Derekp978]

Thank you @amitchandak I will mark this as an anser for now - my final version ended up being slightly different, as I changed the measure beneath the YTD as well - also this is already a Date Table with a relationship to my fact table. 

'Effective Date'[Effective Date])

 
My final version had to consider trying to put 0's in after the hire date and before the term date of the employer and to specifically only look at certain employees.  Here is my current version for reference: (You can see some of your codes influence)

Total Quota Attainment:= 

VAR _min_hired = CALCULATE(SELECTEDVALUE('Opportunity Owner'[Hired Date]),FILTER('Opportunity Owner', NOT ISBLANK('Opportunity Owner'[Hired Date])))
VAR _max_hired = CALCULATE(MAX(SELECTEDVALUE('Opportunity Owner'[Termination Date]),TODAY()),FILTER('Opportunity Owner', NOT ISBLANK('Opportunity Owner'[Hired Date])))
VAR enddate = TODAY()
VAR currentdate = FILTER( VALUES('Effective Date'[Effective Date]) , 'Effective Date'[Effective Date] <= enddate)

VAR r =  CALCULATE([Total Expansion Value]+[Total New ARR] + 0
, 'Opportunity Owner'[Opportunirty Owner Title] = "Director of Sales"
)
VAR c = CALCULATE([t1])

RETURN IF(MIN('Effective Date'[Effective Date]) >= _min_hired && MIN('Effective Date'[Effective Date]) < _max_hired  && (NOT ISBLANK(_min_hired) || c > 0), r, BLANK())