YTD Average that only shows YTD to today for this year, and Total YTD for alst

[Anonymous]

I have a trend line where I am creating a tool tip to work off of.  One of the things I want to show is YTD Sales Sum along the trend line but I don't want that number to change during the year.  So as the person moves along the trend line in 2019 they will see current YTD numbers for 2019.  Then when they go into 2018 it will switch to Total YTD for 2018.  

This looked promising but he has the dates hardcoded:  YTD Sums

My current code is as following but it is only grabbing this year's YTD and shows it by week... so it changes.  I also have a non-related calendar table since I use a lot of trending but I don't know if that matters in this instance or not.

I am also taking that number and averaging it out across all locations clicked.  But right now I can't get 1 location to work....  For previous year it would just show the entire year information.  

I've also been messing with this example but the only thing I've accomplished is a weekly YTD average...  that changes each week.

Data and Results I want to see are below:

Location          Date            Sales              YTD Average

A                    1/1/19           100                   187.5

A                     1/8/19           200                  187.5

A                      1/15/19         150                 187.5
A                      1/22/19         300                 187.5

Avg Sales Per Week YTD = 
 

var varYTDAvgSalesPerWeek = 
TOTALYTD(
		AVERAGE(FactRegularSales[Sales]),
		'DatesFilter'[Date],
		USERELATIONSHIP ( 'DIMCalendar'[Date], 'DatesFilter'[Date]  )
	) 

var varAvgSalesPerWeek = 
 AVERAGEX(
	KEEPFILTERS(VALUES('DIMSupplyChainHierarchy'[LocationCode])),
	CALCULATE(varYTDAvgSalesPerWeek)
)
	
RETURN
varAvgSalesPerWeek

[MFelix · Accepted solution]

Hi @Anonymous ,

Looking at your formula you have a USERELATIONSHIP between the DimCalendar and the FilterDate however if you want to calculate the average of the full year of sales the active relationship needs to be between your sales and the Date table you use on your visual.

Why don't you try this measure instead:

CALCULATE (
    AVERAGE ( Sales[Sales] );
    ALL ( Sales[Date]; Sales[Sales] );
    ALLSELECTED ( Sales[Location] )
)

Regards,

MFelix

[Anonymous]

In a table that appears to work however, when I switch it to the tool tip it still changes week to week.  

So the Trend I am using has a datehierarchy going off of my Calendar Dimension.  

I changed the Tool Tip to use the date off of the table it is in (Different table).  

[MFelix]

Hi @ISIGraber,

Can you share a sample file?

Regards,
MFelix

[Anonymous]

Thanks but I ended up not needing to go this far.  The previous answer was good enough.