Forum Discussion
Solved
get previous available date using dax?
im currently computing for gain/loss in the stock market, and the calendar is not perfect. for example today is monday, and i need to retrieve the data for friday. I am trying to do it in dax with date values, but i only have data for weekdays, nothing on weekends. hope it make sense. Anyone can help please and thanks in advance.
SinclairFletche
Try this, replace source with the table name you have:Gain / Loss = VAR x = 'Historical Data'[DATE] RETURN CALCULATE( MAX('Historical Data'[SOURCE), FILTER( ALL('Historical Data'), 'Historical Data'[DATE] < x ) )
4 Replies
- using MAX() might work depending on your use case e.g., if you want the most recent date.
Hi Ray, okay i get the idea, thank you
Gain / Loss =
CALCULATE(
MAX('Calendar'[Date]),
FILTER(
ALL('Historical Data'[DATE]),
'Historical Data'[DATE] < MAX('Calendar'[Date])
)
)
mhmm, im just getting the same date instead of the previous day
what am i doing wrong?SinclairFletche
Try this, replace source with the table name you have:Gain / Loss = VAR x = 'Historical Data'[DATE] RETURN CALCULATE( MAX('Historical Data'[SOURCE), FILTER( ALL('Historical Data'), 'Historical Data'[DATE] < x ) )
