Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
3 years ago
Solved

Slicer not interacting with graph

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 o...
  • Anonymous's avatar
    Anonymous
    3 years ago

    *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: