Slicer not interacting with graph

[codestule]

I have a graph that displays how I want (I can change the dates in a slicer and it adjusts accordingly):

 

When I add a measure that I created, the graph seems to want to display the oldest date from my date table rather than the selected range in my slicer:

I utilize a separate date table than my data table because I have a cumulative measure that won't work properly if I utilize the data table's date.  The DAX for the measure that calculates my cumulative total is as follows (and this seems to work fine by itself until I add the 'zzTestProjectedReduction' measure):

zzProjectedExpTotCum = 
VAR maxDate = MAX('met_mac (2)'[weekEndingExpire])

RETURN
CALCULATE(
    SUM('met_mac (2)'[zProjectedExpTot]),
        FILTER(
            ALL('met_mac (2)'),
            'met_mac (2)'[weekEndingExpire] <= maxDate),
        FILTER(
            ALL('met_mac (2)'),
            'met_mac (2)'[STATION] IN VALUES('Station Breakdown'[STATION]))
)

The DAX for the measure that seems to mess up the graph is as follows:

zzTestProjectedReduction = 
VAR minDate = CALCULATE(MIN('date_calendar (2)'[WeekEnding]),ALLSELECTED('date_calendar (2)'[WeekEnding]))
VAR maxDate = MAX('date_calendar (2)'[WeekEnding])
VAR noWeeks = DATEDIFF(minDate, maxDate, WEEK) + 1
VAR noEmps = SELECTEDVALUE(znoEmpTable[Value])
VAR compTar = SELECTEDVALUE(zweeklyCompTarValues[Value])

RETURN
CALCULATE(
    [zProjectedExpTotSum] - ((noEmps * compTar) * noWeeks),
    'date_calendar (2)'[WeekEnding] <= maxDate
)

Any / all thoughts on what I'm missing or how to go about fixing this would be greatly appreciated, thanks in advance for looking.

[codestule]

@holodan95 ,

Thanks for taking a look.  What I'm trying to accomplish is graphing my projected reduction only for the weeks selected in the slicer / shown in the graph.  So it should look something like this:

I was able to accomplish this before I had to introduce a separate date table however my cumulative formula for my expirations was not calculating correctly:

The DAX for my original projected reduction looked like this:

zTestProjectedReduction = 
VAR minDate = CALCULATE(MIN('met_mac (2)'[weekEndingExpire]),ALLSELECTED('met_mac (2)'[weekEndingExpire]))
VAR maxDate = MAX('met_mac (2)'[weekEndingExpire])
VAR noWeeks = DATEDIFF(minDate, maxDate, WEEK) + 1
VAR noEmps = SELECTEDVALUE('znoEmpTable'[Value])
VAR compTar = SELECTEDVALUE('zweeklyCompTarValues'[Value])

Return
CALCULATE(
    [zProjectedExpTotSum] - ((noEmps * compTar) * noWeeks),
    'met_mac (2)'[weekEndingExpire] <=maxDate
)

So I thought that if I just changed the original table references to my new date table it'd work the same but it doesn't.  Hopefully this additional information is helpful.  Thanks again for taking the time to look / respond.

[holodan95]

Can you possibly show the table connections?

[codestule]

The date table ('date_calendar (2)') is connected to my 'met_mac (2)' table via this relationship:

the tables 'znoEmpTable' and 'zweeklyCompTarValues' are stand alone with no connections (I don't want them in interact with the other tables and cause unwanted filtering.  I think I got everything but if you need more detail, let me know.

[holodan95]

That will surely be a problem. You need to connect all tables with the date table, because the measure will add values to all possible rows - in your case, to every date row. If you connect the date table with the others as well, it will only affect those that are common in both. This is my experience with problems like these.

[holodan95]

Also, you'll have to work from the date table after connecting it to the others.  Basically what you need is this connection:

TABLE1   and DATE_TABLE -->1:Many from DATE_TABLE to TABLE1
TABLE2  and DATE_TABLE -->1:Many from DATE_TABLE to TABLE2

In every measure and slicer, work with the dates from DATE_TABLE

[codestule]

That's weird because those two tables are only used to pass a user selected value into a variable called out in the measure.  As you can see in the one graph I displayed before I separated the dates into a different table,

the graph displayed correctly (light blue line) even with the 'znoEmpTable' and 'zweeklyCompTarValues' tables not having an established relationship to any other tables.  From what I can tell because they have no influence on filtering the data (just passing a number into a variable) there shouldn't need to be a relationship.

Also, both tables are single column tables with 10 values each ( 1-10 ) in one and ( 10, 20... 100) in the other, not really any way to create a relationship.

Thanks for the thoughts/time, I appreciate your efforts.

[codestule]

So after more tinkering with the DAX, I think I'm getting closer...  I put a table in to view the calculation by week ending and the data in the table calculates correctly (as shown below) however it doesn't appear to display on the graph.  I'm not sure if it is somehow getting filtered out on the graph but not in the table?  Any thoughts on things to check/look for?

The calculation executes correctly for each week ending in the selected date range (9/25 - 12/25) as indicated in the table;
9/25-  8060 - 100 = 7960
10/2- 7960 + 60 (incoming expirations) - 100 = 7920
10/9- 7920 + 36 (incoming expirations) - 100 = 7856

...and so on

The incoming expirations are calculated by taking the current week ending expired - previous week ending expired.
10/2 expired = 8120, previous week (9/25) expired = 8060, incoming = 8120-8060 or 60.

Thanks in advance for any help/ideas.

[codestule · Accepted solution]

*SOLVED*
BI Community, Thank you all for your time/support in trying to get this figured out; I could not have figured this out without the collective brainpower that exists in this community so I say it again, thank you all I really appreciate your thoughts/insights/tutelage.  Here is the solution that I came across to accomplish what I was after.

First as suggested by @holodan95 , I did need to have a separate date table with a one to many relationship to my data table.  After doing some digging, I ran across this article Cumulative sum in Power BI: CALCULATE, FILTER and ALL | by Samuele Conti | Medium which explains the madness 🙂 behind 'calculate', 'filter', and 'all' within cumulative formulas.  I apologize I am not sure where I found the 'DATEDIFF' info but as you can see in my DAX I utilize that to determine the number of weeks between two dates.  Lastly, I had two separate tables (without established relationships to each other or the data table) that I was using to pass a user selected value into a variable to perform a calculation.  Below is the finished DAX and a pic of the result.

zzTestProjectedReduction = 
--DEC Ps115:1
VAR minDate = MIN('date_calendar'[WeekEnding])
VAR maxDate = MAX('data_table'[weekEndingExpire])
VAR noWeeks = DATEDIFF(minDate, maxDate, WEEK) + 1
VAR noEmps = SELECTEDVALUE('znoEmpTable'[Value])
VAR compTar = SELECTEDVALUE('zweeklyCompTarValues'[Value])

Return
CALCULATE(
    [zzProjectedExpTotCum] - ((noEmps * compTar) * noWeeks)
)

The projected reduction value is the teal/cyan color:

[holodan95]

Sorry, for not being able to help you 😞

[holodan95]

Hi,

Can you give us an insight on what you want the graph to show, after adding the second measure?

If you add it on it's own, what does it do? Does it mess up the graph, or it shows a legit data that you need?

It's really not the best solution but if you filter your seperate date table in query editor, so it doesn't have dates for 1975 etc, then those very old dates will not show, however it seems that the measure is not working properly, but I'm not sure what it should do.