Filtering dates in two date columns

[Anonymous]

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.

[mahoneypat]

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

[Anonymous]

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... 

[v-lili6-msft]

hi  @Anonymous 

Could you please share your sample pbix file and your expected output, that will be a great help.

Regards,

Lin

[AntrikshSharma]

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
    Result

[Anonymous]

Hi @AntrikshSharma I tried it but th measure returns always the latest Project date independent of the current Report date...

[AntrikshSharma]

Can you share the pbix file, or just the sample data?

[Anonymous]

Hi @AntrikshSharma

 So 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?

[AntrikshSharma]

Summarize can group by columns of mulitple tables, example:

SUMMARIZE(
    Sales,
    Sales[Order Date],
    Sales[Due Date],
    Customer[Birth Date]
)