topic Re: How to replicate the LOOKUP Excel formula in DAX (Excel 2016) in DAX Commands and Tips

How to replicate the LOOKUP Excel formula in DAX (Excel 2016)

JollyRoger01 — Wed, 21 Jul 2021 04:46:03 GMT

I am trying to replicate the LOOKUP formula in Excel that will return the Reporting Period End date for a date that falls between the Reporting Period Start Date and Reporting Period End Date. This is a fairly simple calc, but I have no idea how to do this in DAX. LOOKUPVALUE is not what I am looking for as it doesn't do greater than or equal two, that I know of. Workbook is below.

Book1

Re: How to replicate the LOOKUP Excel formula in DAX (Excel 2016)

wdx223_Daniel — Wed, 21 Jul 2021 05:09:28 GMT

Re: How to replicate the LOOKUP Excel formula in DAX (Excel 2016)

Jihwan_Kim — Wed, 21 Jul 2021 05:14:42 GMT

end date in the period : =
IF (
HASONEVALUE ( Dates[Date] ),
MAXX (
FILTER (
Periods,
MAX ( Dates[Date] ) >= Periods[Reporting Period Start]
&& MIN ( Dates[Date] ) <= Periods[Reporting Period End]
),
Periods[Reporting Period End]
)
)

Link to the PBIX file

Re: How to replicate the LOOKUP Excel formula in DAX (Excel 2016)

JollyRoger01 — Wed, 21 Jul 2021 05:53:11 GMT

I have tried this, after changing all the table names to match mine, but I cannot get it to work (all the values are blank). I am adding it to a calculated column to Table_Historical.

Re: How to replicate the LOOKUP Excel formula in DAX (Excel 2016)

JollyRoger01 — Wed, 21 Jul 2021 05:54:24 GMT

I am looking to add this as a calculated column called Reporting Period End Dtate in Table_Historical. Do you know how I can do this, using the same names I have used?

Re: How to replicate the LOOKUP Excel formula in DAX (Excel 2016)

JollyRoger01 — Wed, 21 Jul 2021 06:19:47 GMT

This is the formula I was looking for:

= CALCULATE ( VALUES ( XREF_ReportingPeriods[Reporting Period End] ), FILTER ( XREF_ReportingPeriods, [Date] >= XREF_ReportingPeriods[Reporting Period Start] && [Date] <= XREF_ReportingPeriods[Reporting Period End] ) )