cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
Advocate II
Advocate II

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:

 

RowPtIDValue DateValue
112309/30/20100
212309/05/20102
312309/01/20101
412308/28/2099

 

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
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.

View solution in original post

2 REPLIES 2
Highlighted
Community Support
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.

View solution in original post

Highlighted
Super User III
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
https://www.linkedin.com/in/excelenthusiasts/

Helpful resources

Announcements
Community Conference

Power Platform Community Conference

Check out the on demand sessions that are available now!

Community Conference

Microsoft Power Platform Communities

Check out the Winners!

secondImage

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