DAX Power BI - Show latest record according to date filter

Question

I have 2 tables in Power BI model, [Date] and [Quota]

• 'Date'[YYYYMM] is used as a period filter on the report
• I want to show the latest quota according to the maximum date of the chosen period

How can I write it in DAX / Calculated Column?

Example:

If I select YYYYMM = "202005" in filter.
Below table should show:

|product|quota|
|A      |10   |
|B      |20   |

Quota Table:

|product|quota|effectiveDate|
|A      |10   |2020-01-01   |
|B      |20   |2020-01-01   |
|A      |25   |2021-01-01   |

Date Table:

|Date      |YYYYMM|
|2020-01-01|202001|
|2020-01-02|202001|
...
|2021-06-09|202106|

To get the latest quota, in SQL it would be:

SELECT TOP (1) q.Quota 
FROM [Quota] q 
LEFT JOIN [Date] d on d.[Date] >= q.effectiveDate 
ORDER BY q.effectiveDate desc

Answer 1

Measure in DAX:

FlagToFilter = 
var __ChoicedDate = CALCULATE(MAX(DateTable[Date      ]), FILTER(ALL(DateTable), SELECTEDVALUE(DateTable[YYYYMM]) = DateTable[YYYYMM] ))
var __MaxFor = CALCULATE(max(QuotaTable[effectiveDate]), FILTER(ALL(QuotaTable), QuotaTable[effectiveDate] <= __ChoicedDate && SELECTEDVALUE(QuotaTable[product])= QuotaTable[product] ))

return
CALCULATE( countrows(VALUES(QuotaTable[product])), FILTER(ALL(QuotaTable[effectiveDate]), SELECTEDVALUE(QuotaTable[effectiveDate]) = __MaxFor))