Forum Discussion
Filtering dates in two date columns
Hi, I can't get my head around filtering of date columns. I have two columns - Report date and Project date.
When I choose a Report date (say 04.07), I would like to see the latest Project date in another visual (say the latest was on 01.07) but not all the project dates before that (say I have 28.06 and 13.01 dates in the Project column as well) and also exclude any project dates after 04.07.
8 Replies
Maybe try something like this:
Measure = VAR LastVisibleDate = MAX ( Sales[Due Date] ) VAR Result = CALCULATE ( MAX ( Sales[Order Date] ), FILTER ( SUMMARIZE ( Sales, Sales[Order Date], Sales[Due Date] ), Sales[Order Date] <= LastVisibleDate ) ) RETURN ResultSo the Due Date = Report date and Order Date = Project date or other way around?
What is they are in different tables? How would the line 8 Summarize look like?
Summarize can group by columns of mulitple tables, example:
SUMMARIZE( Sales, Sales[Order Date], Sales[Due Date], Customer[Birth Date] )
Hi AntrikshSharma I tried it but th measure returns always the latest Project date independent of the current Report date...
- Can you share the pbix file, or just the sample data?
Are the Report Date and Project Date columns in the same table? In any case, here is an expression you can try
Latest Project Date = VAR __thisreportdate = SELECTEDVALUE ( Table[ReportDate] ) RETURN CALCULATE ( MAX ( Table[ProjectDate] ), ALL ( Table[ReportDate] ), Table[ReportDate] <= __thisreportdate )If this works for you, please mark it as the solution. Kudos are appreciated too. Please let me know if not.
Regards,
Pat
Hi mahoneypat Thanks! The report date column has been copied into the same column. The measure, however, returns always the latest Project date independent of the current Report date...
hi Anonymous
Could you please share your sample pbix file and your expected output, that will be a great help.
Regards,
Lin
