Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
2 years ago
Solved

Filter dates before or equal a selected value in a segment w/o using date slicer

I have a visual in PowerBI showing values by quarter and I want to show only the quarter that are before or equal to the selected date in a segment, how to do that ?

 

When I use the segment the interaction on the other visuals filter only on the equal selection. I want the dates filtered before or equal the selection. I don't want to use the date silder because I want the selection to be only by quarter and not on a specific date. 

 

Below is a screenshot of what I'm looking for : 

(i) par Année, Trim show the current behavior based on the selection

(ii) Somme de Numbers par Année, Trim shows what I would like to do

 

I tried this query but it doesn't work 

Total Numbers =
CALCULATE(
    SUM('T_Data_1'[Numbers]),
    FILTER(
        T_Data_1,
        T_Data_1[Date] <= MAX(T_Dates[TrimestreDate])
    )
)

I also tried Dynamic Parameter M but I'm not using Direct Query so I wasn't able to bind the paramater and my report will be published on PowerBI Report Server that doesn't support the feature. 

 

This is my model (I wasn't able to attached the .pbix file)

Need Help
  • amitchandak's avatar
    amitchandak
    2 years ago

    Anonymous , You need to use a disconnected/independent date table

     

    example

     

    //Date1 is independent Date table and Date is Joined
    new measure =
    var _max = maxx(allselected(Date1),Date1[Date])
    return
    calculate( sum(Table[Value]), filter('Date',  'Date'[Date] <=_max))

     

    Need of an Independent Date Table:https://www.youtube.com/watch?v=44fGGmg9fHI

     

     

2 Replies

  • amitchandak's avatar
    amitchandak
    Super User
    2 years ago

    Anonymous , You need to use a disconnected/independent date table

     

    example

     

    //Date1 is independent Date table and Date is Joined
    new measure =
    var _max = maxx(allselected(Date1),Date1[Date])
    return
    calculate( sum(Table[Value]), filter('Date',  'Date'[Date] <=_max))

     

    Need of an Independent Date Table:https://www.youtube.com/watch?v=44fGGmg9fHI