Filter a table on selected date value and find max date field records

[yusufashiq]

I have a Table

I need the result table similar to use in SQL Server, the script is 

 SELECT CAST(t.EmpNo AS int) AS EMpNum, t.ProjTo AS ToDept, t.TransferDate, t.TrType
FROM dbo.Transfer AS t RIGHT OUTER JOIN
(SELECT CAST(EmpNo AS int) AS EMpNum1, MAX(TransferDate) AS MaxDate
FROM dbo.Transfer
WHERE (TransferDate <= '2022-01-31') and EmpNo=71103
GROUP BY CAST(EmpNo AS int)) AS tm ON CAST(t.EmpNo AS int) = CAST(tm.EMpNum1 AS int) AND t.TransferDate = tm.MaxDate
WHERE (t.TrType = 1)
If my Selected Value (is Payroll Period='2022-01-31') then

 the result is

where I need to use the TransferDate condition like a variable to filter based on selected date value using DAX.
If my Selected Value (is Payroll Period='2022-02-28') then

the result is 

How to achieve this table result same as SQL Server in Power BI Transform Data

Note: The selected value is a variable from the Visual selection

[v-yangliu-msft · Accepted solution]

Hi  @yusufashiq ,

Here are the steps you can follow：

1. Create calculated table.

Date =
CALENDAR(DATE(2021,1,1),DATE(2022,12,31))

2. Create measure.

Flag =
var _select=SELECTEDVALUE('Date'[Payroll Period])
var _if=CALCULATE(MAX('Table'[TransferDate]),FILTER(ALL('Table'),'Table'[TrType]=1&&'Table'[EmpNo]=71103&&'Table'[TransferDate]<=_select))
return
IF(
    MAX('Table'[TrType])=1&& MAX('Table'[EmpNo])=71103&&MAX('Table'[TransferDate])=_if,1,0)

3. Place [Flag]in Filters, set is=1, apply filter.

4. Result:

Take the [Payroll Period] of the Date table as the slicer.

When the selection is: 2022.1.31

When selected as: 2022.2.28

Best Regards,

Liu Yang

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly