Forum Discussion
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 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!
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_DateWith 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.
4 Replies
- amitchandak
Super User
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)) 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:
Regards,
Xiaoxin Sheng
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.
- 1st table among other columns have accumulative hours consmption and dates of the consumptions.
- 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.
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_DateWith 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.
