Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
3 years ago

show filter datas based on slicer selection

Hello All,

 

I have report having data like below for last 400 days.

 

DateSales
1/1/2022100
1/2/2022200
1/3/2022300
1/4/202234
1/5/202278
1/6/202256
1/7/202243
1/8/202245
1/9/202267
1/10/202289
1/11/202234
1/12/202256
1/13/202234
1/14/202267
1/15/202254
1/16/202267
1/17/202254

----------------------------------------------------------------

 

Now my requirement is I need to show a slicer which has 3 values.:Last 30 Days,Last 12 Weeks and Last 12 Months.

So I created that.

Now If the user select Last 30 Days ,I need to show a filter which contains last 30 days in it.

Similarly if the user selects Last 12 Weeks I need to show a filter for last 12 weeks filter (week stats from sunday)

if the user selects Last 12 MonthsI need to show a filter for last 12 Months filter.

 

So based on selected filter value my sales should change.

 

 

 

Need Help

5 Replies

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

    Anonymous , Try what I show in blog

     

    https://medium.com/chandakamit/power-bi-when-i-felt-lazy-and-i-needed-too-many-measures-ed8de20d9f79

     

     

    Or create three measures and use field parameters

    Power BI Field Parameters — A Quick way for Dynamic Visuals: https://amitchandak.medium.com/power-bi-field-parameters-a-quick-way-for-dynamic-visuals-fc4095ae9afd

     

     

    Time Intelligence, DATESMTD, DATESQTD, DATESYTD, Week On Week, Week Till Date, Custom Period on Period,
    Custom Period till date: https://youtu.be/aU2aKbnHuWs&t=145s

     

    Rolling Days Formula: https://youtu.be/cJVj5nhkKBw

    Rolling Months Formula: https://youtu.be/GS5O4G81fww

     

    • Anonymous's avatar
      Anonymous
      Not applicable
      3 years ago

      Hi amitchandak ,

       

      Could you pleasde let me know which one I need to follow in my case

  • Jihwan_Kim's avatar
    Jihwan_Kim
    Icon for Super User rankSuper User
    3 years ago

    Hi,

    Please check the below picture and the attached pbix file.

    I tried to create a sample pbix file like below.

    I hope the below can provide some ideas on how to create a solution for your datamodel.

     

     

     

     

     

    Value by slicer: =
VAR _last30days =
    FILTER (
        ALL ( 'Calendar' ),
        'Calendar'[Date] <= TODAY ()
            && 'Calendar'[Date]
                >= TODAY () - 29
    )
VAR _currentweekend =
    MAXX (
        FILTER ( ALL ( 'Calendar' ), 'Calendar'[Date] = TODAY () ),
        'Calendar'[End of Week]
    )
VAR _last12weeks =
    FILTER (
        ALL ( 'Calendar' ),
        'Calendar'[End of Week] <= _currentweekend
            && 'Calendar'[End of Week] >= _currentweekend - 7 * 11
    )
VAR _last12months =
    FILTER (
        ALL ( 'Calendar' ),
        'Calendar'[End of Month] <= EOMONTH ( TODAY (), 0 )
            && 'Calendar'[End of Month] >= EOMONTH ( TODAY (), -11 )
    )
RETURN
    SWITCH (
        SELECTEDVALUE ( Slicer[Slicer] ),
        "Last 30 Days", CALCULATE ( SUM ( Data[Value] ), KEEPFILTERS ( _last30days ) ),
        "Last 12 Weeks", CALCULATE ( SUM ( Data[Value] ), KEEPFILTERS ( _last12weeks ) ),
        "Last 12 Months", CALCULATE ( SUM ( Data[Value] ), KEEPFILTERS ( _last12months ) ),
        SUM ( Data[Value] )
    )

     

    • Anonymous's avatar
      Anonymous
      Not applicable
      3 years ago

      Hi Jihwan_Kim ,

       

      Thank for your answer.

      But I need to show dates as a filter for end user when I selected slicer value.

      Also if they select Monthly I need to show month start dates like below.

      And If they select any month i need to show the sum of sales for that month (totals days sales in month)

       

      1/1/2022
      2/1/2022
      3/1/2022
      4/1/2022
      5/1/2022
      6/1/2022
      7/1/2022
      8/1/2022
      9/1/2022
      10/1/2022

       

      Similarly when they selected weekly I need to show last 12 weeks data.

      And If they select any week i need to show that week sum of sales

       

      9/4/2022
      9/11/2022
      9/18/2022
      9/25/2022
      10/2/2022
      10/9/2022
      10/16/2022
      10/23/2022
      10/30/2022
      11/6/2022
      • Jihwan_Kim's avatar
        Jihwan_Kim
        Icon for Super User rankSuper User
        3 years ago

        Hi,

        Thank you for your feedback.

        I am not sure whether I understood your question correctly, but please check the attached pbix file if it suits your requirement.

        I tried to create FIELD Parameter and connect to the slicer table, like the below.

        This field parameter makes Axis dynamic as per the selection of the slicer.