Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Allisond
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
v-shex-msft
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
Ashish_Mathur
Super User
Super User

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/
v-shex-msft
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.

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.