Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
3 years ago

Show data based on slicer selection

Hello All,

 

I have table where I am having daily data like date,sales,profit and discount.

 

My requirement is I need to show a slicer which have values like daily,weekly,monthly and Quarterly.

If the user selects daily I need to show last 30 days sales,profit and discount.

If the user selects Monthly I need to show last 12 months data.(Here I need to aggrigate data for monthly)

If the user selects Weekly I need to show last 12 Weeks data. (Here I need to aggrigate data for Weekly)

If the user selects Quarterly I need to show last 4 qtrs data. (Here I need to aggrigate data for qtrly)

 

So I created a table in power bi with these values and showing as slicer.

Not sure how to write the dax to calulate these.

 

 

Sample Data :

 

 

Could any one please help on this

Need Help

3 Replies

  • amitchandak's avatar
    amitchandak
    Super User
    3 years ago

    Anonymous , Have these as the name of Date, Month , week and Qtr column, you want to display on axis

     

    rolling 12 =  // Same for month and qtr
    var _max = if(isfiltered('Date'),MAX( 'Date'[Date]) , today())
    var _min = date(Year(_max), month(_max) -12, Day(_max))+1
    BLANK())
    return
    CALCULATE([net] ,filter(Date, Date[Date] >= _min && Date[Date] <=_min))

     

     

    days

    =

    var _max = if(isfiltered('Date'),MAX( 'Date'[Date]) , today())
    var _min = _max -30
    BLANK())
    return
    CALCULATE([net] ,filter(Date, Date[Date] >= _min && Date[Date] <=_min))

     

     

    for week you need use week rank column in date table

    Week Rank = RANKX('Date','Date'[Week Start date],,ASC,Dense)
    OR
    Week Rank = RANKX('Date','Date'[Year Week],,ASC,Dense) //YYYYWW format

     

    Last 12 weeks = CALCULATE(sum('Table'[Qty]), FILTER(('Date'),'Date'[Week Rank]>=max('Date'[Week Rank])-11 && 'Date'[Week Rank]<=max('Date'[Week Rank])))

     

    Now use the field parameter on the renamed column of date table and change the measure as suggested in blog

     

    selected parameter code is important

    Switch TOPN with Field Parameters: https://amitchandak.medium.com/switch-topn-with-field-parameters-299a0ae3725f

     

    • Anonymous's avatar
      Anonymous
      Not applicable
      3 years ago

      Hi amitchandak ,

       

      In the below calculation what is meant by [net] ?

       

      CALCULATE([net] ,filter(Date, Date[Date] >= _min && Date[Date] <=_min))

      • amitchandak's avatar
        amitchandak
        Super User
        3 years ago

        Anonymous , here net is my measure. You can use your measure