Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
4 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
    4 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.
     
Anonymous's avatar
Anonymous
Not applicable
4 years ago

hi amitchandak ,

Many thanks for the reply. I think I did not explain enough what I'm looking to achive.

The two lines shown in the line chart are from two different tables.

  1. 1st table among other columns have accumulative hours consmption and dates of the consumptions.
  2. 2nd tables has the progress of the project and its date.

I was able to connect and put the two graph together. but I wanted to limit the time windows by a maximum date and a minimum date taken from the first table (hours consumption)

 

so instead of showing 0 progress 3 or 4 months before the consumptions starts. I want to see the two graph from the moment I have hours consumption.

 

for that reason I've calculated the start date and end date of each project but I don't know how to apply this into the line chart.

 

Example of another project:

Not pleaseant view:

 

Desired automatic view:

 

 

Thanks for your help 🙂

 

My best regards, 

Abdel.

  • Anonymous's avatar
    Anonymous
    Not applicable
    4 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.