Forum Discussion
Slicer not interacting with graph
*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:
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.
*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: