cancel
Showing results for
Did you mean:
Highlighted ## Finding the difference in value within a date window AND closest to a given date

Hello,

I am in healthcare and needing a calculation that will return a value with 2 filters:

- Date Window  ( - 28 to 32 days)

AND

- Closest to 30 days if there are multiple values

So that I can find the difference between the 2 values.

Example Table:

 Row PtID Value Date Value 1 123 09/30/20 100 2 123 09/05/20 102 3 123 09/01/20 101 4 123 08/28/20 99

I would want the value returned to come from Row 3 (101) so I could base my diff calculation on that value (100 -101).

Any direction is much appreciated.

1 ACCEPTED SOLUTION

Accepted Solutions
Highlighted Community Support

Hi @Allisond,

It sounds like you want to find out the previous records based on the current record date and category right? If this is a case, you can use the following formula to find out the previous date and use the current category and calculated date to find out the previous record.

``````Measure =
VAR currDate =
MAX ( Table[Value Date] )
VAR prevDate =
CALCULATE (
MAX ( Table[Value Date] ),
FILTER ( ALLSELECTED ( Table ), [Value Date] < currDate ),
VALUES ( Table[PtID] )
)
RETURN
CALCULATE (
CALCULATE (
SUM ( Table[Value] ),
FILTER ( ALLSELECTED ( Table ), [Value Date] = currDate )
)
- CALCULATE (
SUM ( Table[Value] ),
FILTER ( ALLSELECTED ( Table ), [Value Date] = prevDate )
),
VALUES ( Table[PtID] )
)
``````

Regards,

Xiaoxin Sheng

Community Support Team _ Xiaoxin
If this post helps, please consider accept as solution to help other members find it more quickly.
2 REPLIES 2
Highlighted Community Support

Hi @Allisond,

It sounds like you want to find out the previous records based on the current record date and category right? If this is a case, you can use the following formula to find out the previous date and use the current category and calculated date to find out the previous record.

``````Measure =
VAR currDate =
MAX ( Table[Value Date] )
VAR prevDate =
CALCULATE (
MAX ( Table[Value Date] ),
FILTER ( ALLSELECTED ( Table ), [Value Date] < currDate ),
VALUES ( Table[PtID] )
)
RETURN
CALCULATE (
CALCULATE (
SUM ( Table[Value] ),
FILTER ( ALLSELECTED ( Table ), [Value Date] = currDate )
)
- CALCULATE (
SUM ( Table[Value] ),
FILTER ( ALLSELECTED ( Table ), [Value Date] = prevDate )
),
VALUES ( Table[PtID] )
)
``````

Regards,

Xiaoxin Sheng

Community Support Team _ Xiaoxin
If this post helps, please consider accept as solution to help other members find it more quickly.
Highlighted Super User III

Hi,

If today is October 2, 2020, the date range of -28 to 32 days is October 1, 2020 to October 5, 2020.  There is no such data in the sample that you selected.  Please clarify.

Regards,
Ashish Mathur
http://www.ashishmathur.com Announcements #### Power Platform Community Conference

Check out the on demand sessions that are available now! #### Create an end-to-end data and analytics solution

Learn how Power BI works with the latest Azure data and analytics innovations at the digital event with Microsoft CEO Satya Nadella. Top Solution Authors
Top Kudoed Authors
Users online (810)