Return data for nearest date 3 months prior

[Zyg904]

Hi all,
I'm a newbie to PowerBI and struggling with a look-up for a report.  By way of introduction, I work in a Care Home group and we're using Power BI to analyse some data we're collecting.  

I have a series of individuals who are weighed throughout the month - there is no consistency on dates weighed and multiple people can be weighed on the same day.  I have date (in dd/mm/yyy hh/mm/ss format) of dates weighed.  I have the value of the weight.  What I'd like is the value of the weight for each individual 3 months prior.  

Eg
Resident Name                               Date Weighed                 Weight        Nearest Date 3 months prior           Weight 3 months prior
Bob                                                  01/01/19                         60                        10/11/18                                       55

Jo                                                     10/01/19                         60                         01/01/19                                      58

What I'd like to know is how do you find out from [date weighed] the [nearest date 3 months prior] and, from that, lookup the [weight 3 months prior]?

Thanks in advance

[v-eachen-msft · Accepted solution]

Hi @Zyg904 ,

At first, you need to create a calendar table which contains date you need. Then use DATEADD() function to get [nearest date 3 months prior]. The DAX is like this:

DATEADD(Table[You date column],-3,MONTH)

Then use LOOKUPVALUE() function to get the weight.