Forum Discussion

maserr's avatar
maserr
Icon for Helper IV rankHelper IV
6 years ago
Solved

Filtering by month and previous ones

Dear all,

 

I would like to filter on my slice by month and that my visuals showing all months of the year until the one chosen. If it is february I would like to see on the graphics the months od January and February.

 

Thanks you,

Need Help
  • v-easonf-msft's avatar
    v-easonf-msft
    6 years ago

    Hi , maserr 

    Here I take four months as an example.  
    pbxi attached

     

    Try steps as below:

    1.Create a calculate  table as a slicer:

    Slicer = DATATABLE("Value",STRING,{{"1January"},{"2February"},{"3March"},{"4April"}})

    2.Create a visual control measure" show Control"  as below and put the measure into visual filters:

     

    Show Control = 
Var YM =
    SELECTEDVALUE(financials[Date])

Return  
    SWITCH(
        SELECTEDVALUE(Slicer[Month]),
        "1January Month",IF(
            MONTH(YM)<=1,1,-1),
        "2February",IF(
            MONTH(YM)<=2,1,-1),
        "3March",IF(
            MONTH(YM)<=3,1,-1),
        "4April",IF(
            MONTH(YM)<=4,1,-1),
            1
    )

     

     

     

    3.it  will show as below:

     

    Best Regards,
    Community Support Team _ Eason
    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

3 Replies

  • amitchandak's avatar
    amitchandak
    Icon for Super User rankSuper User
    6 years ago

    Try. Assuming you month is coming from the date dimension

    measure =
var _max = maxx('Date','Date'[Date])
var _min = date(year(_max),month(_max)-1,1)

return 
calculate([measure],filter(all('Date'), 'Date'[Date]>= _min && 'Date'[Date]<= _max))
    • maserr's avatar
      maserr
      Icon for Helper IV rankHelper IV
      6 years ago

      Thanks for your quick response, but which are the next steps once i have created the measure? it does not work on my visual.

       

      Thanks another time

      • v-easonf-msft's avatar
        v-easonf-msft
        Icon for Community Support rankCommunity Support
        6 years ago

        Hi , maserr 

        Here I take four months as an example.  
        pbxi attached

         

        Try steps as below:

        1.Create a calculate  table as a slicer:

        Slicer = DATATABLE("Value",STRING,{{"1January"},{"2February"},{"3March"},{"4April"}})

        2.Create a visual control measure" show Control"  as below and put the measure into visual filters:

         

        Show Control = 
Var YM =
    SELECTEDVALUE(financials[Date])

Return  
    SWITCH(
        SELECTEDVALUE(Slicer[Month]),
        "1January Month",IF(
            MONTH(YM)<=1,1,-1),
        "2February",IF(
            MONTH(YM)<=2,1,-1),
        "3March",IF(
            MONTH(YM)<=3,1,-1),
        "4April",IF(
            MONTH(YM)<=4,1,-1),
            1
    )

         

         

         

        3.it  will show as below:

         

        Best Regards,
        Community Support Team _ Eason
        If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.