Line Charts and SWITCH Function

[Anonymous]

Hi All, 

I am creating a line chart to show a trend line for company specific metrics. I have used the SWITCH function to construct the formula seem below: 

Formula =

VAR Actual_Metric = DIVIDE(SUM(Budget[Budget]),SUM(Actual1[Actual])

VAR FCST_Metric = DIVIDE(SUM(Budget[Budget]),SUM(Forecasted_Actual1[Actual])

RETURN

SWITCH(
TRUE(),

[Max_Actual1_Date] >= SELECTEDVALUE(s_Date[Date]),        Actual_Metric,

[Max_Actual1_Date] < SELECTEDVALUE(s_Date[Date]),          FCST_Metric,

                                                                                                FCST_Metric
)

The goal here is to use 'SUM(Actual1[Actual])' for the dates that are in our table and for anything outside of that range, use the 'SUM(Forecasted_Actual1[Actual])' data. This formula works on a table at a row level but when using on a clustered line/column chart it does not extend the line at a monthly level to the future dates where the FCST_Metric func. would be evaluated.

For example. the months of November and December do not display the line at a monthly level but if filtered down to a daily level the line magically shows up. I am trying to understand/fix this issue so that this line is shown at a monthly level and continuous, currently it appears disjoint despite the data itself being continuous. Additionally, the measure is summing the total daily values on my CARD visual whereas it should be the aggregated average. Any fixes on this for my formula? 

Additional Context:
s_Date is a M-language created date table that feeds all tables in my schema. The Budget, Actual1 and Forecasted_Actual1 tables are all connected to the s_Date table. The measure [Max_Actual1_Date] returns the max date from the Actual1 table. 

Thank you for your time and patience! 
 

[AlexisOlson · Accepted solution]

---

@Anonymous wrote:

For example. the months of November and December do not display the line at a monthly level but if filtered down to a daily level the line magically shows up.

---

The SELECTEDVALUE function will return a blank if there are multiple values within the current filter context. If you're grouping at the month level, then you'll get a blank since there isn't a single value to return. You need to tell it what date you want to compare to when multiple are available. One way to do this is to use MAX instead of SELECTEDVALUE.

[Anonymous]

Hi Alexis, this fixed both of my problems, thank you for the tip and insight. I will remember this for the future!