Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
3 years ago
Solved

Set a Maximum and Minimum date filter on a Line Chart

Dear community,    I'm struggling on setting up a filter to show only date between a calculated Minimum date and a Maximum date. This dynamic filter must be applied on the line chart, means for ea...
  • Anonymous's avatar
    Anonymous
    3 years ago

    Found a solution, 

    I've created new column in the table Progress(Sheet2) called P_Date.

     

    P_Date =
    var DateMAx= MAXX(filter('ConsumptionTable','ConsumptionTable'[Job_NO]=earlier('Sheet1 (2)'[Job_No])),'ConsumptionTable'[MAX_Date])
    var DateMin= MAXX(filter('ConsumptionTable','ConsumptionTable'[Job_NO]=earlier('Sheet1 (2)'[Job_No])),'ConsumptionTable'[MIN_Date])
    var P_Date=
    IF('Sheet1 (2)'[Posting_Date]<=DateMAx&&'Sheet1 (2)'[Posting_Date]>DateMin,'Sheet1 (2)'[Posting_Date],Blank())
    Return P_Date
     
    With P_Date I limit the amount of values that neeed to be shown in the linechart taking into account the Max_Date and Min Date from the other table ConsumptionTable.
     
    I hope this example can help others to filter the line chart based on Max date and Min Date.
     
    thanks,
    Best regards.
     
amitchandak's avatar
amitchandak
Icon for Super User rankSuper User
3 years ago

Anonymous , not very clear. But you need measure like

 

new measure =
var _max = maxx(allselected(Date),Date[Date])
var _min = minx(allselected(Date),Date[Date])
return
calculate( sum(Table[Value]), filter('Date',  'Date'[Date] =_max)) - calculate( sum(Table[Value]), filter('Date',  'Date'[Date] =_min))