topic Re: DAX to pick data based on previous latest date with values in DAX Commands and Tips

DAX to pick data based on previous latest date with values

vkboddapati — Fri, 03 Sep 2021 15:33:34 GMT

Need Help on the below!

I have a factTable like below:

Date	A	B
01-09-2020	1,50,000	2,00,000
06-09-2020	1,30,000	1,80,000
10-09-2020	1,70,000	2,15,000
20-09-2020	1,50,000	2,00,000
26-09-2020	1,22,500	1,33,500
27-09-2020	2,00,000	3,20,000
28-09-2020	2,20,000	4,50,000
02-10-2020	1,50,000	2,00,000
07-10-2020	1,30,000	1,80,000
11-10-2020	1,70,000	2,15,000
21-10-2020	1,50,000	2,00,000
27-10-2020	1,22,500	1,33,500
28-10-2020	2,00,000	3,20,000
29-10-2020	2,20,000	4,50,000

Q: Need a measure to return the values from most recent date of the above table based on my Date selection (Date Table) in the Filter!

example: If I choose any date between: 02-09-2020 to 05-09-2020 in the filter (of Date Table), the measure shall return the values of 01-09-2020 (i.e. 1,50,000 and 2,00,000); similarly if I choose, 27-10-2020 in the filter, I shall get 1,22,500 and 1,33,500 and if I choose, 28-10-2020 in the filter, I shall get 2,00,000 and 3,20,000 against selection.

Thanks in advance!

Re: DAX to pick data based on previous latest date with values

Greg_Deckler — Fri, 03 Sep 2021 16:39:15 GMT

vkboddapati Try using a Complex Selector: The Complex Selector - Microsoft Power BI Community

For example:

Selector = VAR __SelectedDate = MAX('Dates'[Date]) VAR __CurrentDate = MAX('Table8'[Date]) VAR __MaxDate = MAXX(FILTER(ALL('Table8'),'Table8'[Date] <= __SelectedDate),[Date]) RETURN IF(__CurrentDate = __MaxDate,1,0)

Re: DAX to pick data based on previous latest date with values

Anonymous — Fri, 03 Sep 2021 16:41:37 GMT

Hi,
Please try something like a code below.

LastAvailableRevenue =

VAR LastDateInContext =

MAX ( Dates[Date] )

VAR LastAvailableDate =

CALCULATE ( MAX ( Orders[Order Date] ), Orders[Order Date] <= LastDateInContext, REMOVEFILTERS ( Dates[Date] ) )

VAR Result =

CALCULATE ( [Revenue], Dates[Date] = LastAvailableDate )

RETURN

Result

Best regards,

Kamil

Re: DAX to pick data based on previous latest date with values

vkboddapati — Sat, 04 Sep 2021 06:18:55 GMT

Anonymous and Greg_Deckler Thanks to both of you! I have mix-matched both of your ideas and got the desired result!
Thank you again for your great help!