Set a Maximum and Minimum date filter on a Line Chart

[Anonymous]

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 each Job_No in the table has a different MIN Date and MAX Date.

Example for this particular job: this chart instead of being shown like this:

it should be shown like this:

The data comes from this table:

Many thanks!

[Anonymous]

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 · Accepted solution]

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]

HI @Anonymous,

This sound like common multiple date fields analysis requirements, you can take a look at the following blog 'start date', 'end date' parts if it helps:

Before You Post, Read This 

Regards,

Xiaoxin Sheng

[amitchandak]

@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))