Forum Discussion
Return data for nearest date 3 months prior
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
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.
1 Reply
- v-eachen-msft
Community Support
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.