Forum Discussion

henry24007's avatar
henry24007
Helper I
4 years ago
Solved

slicer problem

 

I have a  year slicer for controling matrix 1, and start year and end year slicer for controlling matrix 2 which is used to calculate the difference of the selected year. My question is how can I use the start year and end year slicer to control the matrix 1 instead of using the year slicer or vice versa?

 

 

Below is the measure of matrix 2 or start/end year:

 

diff =
var start_year = SELECTEDVALUE('Start Year'[year])
var end_year = SELECTEDVALUE('End Year'[year])

RETURN
IF( end_year - start_year < 0, "Invalid Selection",
CALCULATE(SUM(Score[Value]),Score[Year] == start_year) -
CALCULATE(SUM(Score[Value]),Score[Year] == end_year)
)
 
 
 
Start Year = ALLNOBLANKROW('year'[year])
 
 
End Year = ALLNOBLANKROW('year'[year])
Need Help
  • amitchandak's avatar
    amitchandak
    4 years ago

    henry24007 , I have discussed it in this video

    Select data between months - https://youtu.be/nEt7dT3Tfv4

     

    Date Range Using 2 slicer =
    var _max = minx(allselected(Date1), Date1[year])
    var _min = maxx(allselected(Date2), Date2[year])
    return
    calculate(countrows(Table), Filter(Table,year(Table[Date]) <=_max && Year(Table[Year]) >=_min) )

     

    or

     

    Date diff  =
    var _max = minx(allselected(Date1), Date1[year])
    var _min = maxx(allselected(Date2), Date2[year])
    return
    calculate(countrows(Table), Filter(Table,year(Table[Date]) =_max))- calculate(countrows(Table), Filter(Table,   Year(Table[Year]) >=_min) )

     

    For second code

    How to use two Date/Period slicers

    https://youtu.be/WSeZr_-MiTg

     

1 Reply

  • amitchandak's avatar
    amitchandak
    Super User
    4 years ago

    henry24007 , I have discussed it in this video

    Select data between months - https://youtu.be/nEt7dT3Tfv4

     

    Date Range Using 2 slicer =
    var _max = minx(allselected(Date1), Date1[year])
    var _min = maxx(allselected(Date2), Date2[year])
    return
    calculate(countrows(Table), Filter(Table,year(Table[Date]) <=_max && Year(Table[Year]) >=_min) )

     

    or

     

    Date diff  =
    var _max = minx(allselected(Date1), Date1[year])
    var _min = maxx(allselected(Date2), Date2[year])
    return
    calculate(countrows(Table), Filter(Table,year(Table[Date]) =_max))- calculate(countrows(Table), Filter(Table,   Year(Table[Year]) >=_min) )

     

    For second code

    How to use two Date/Period slicers

    https://youtu.be/WSeZr_-MiTg