Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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.
Solved! Go to Solution.
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
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.
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
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
107 | |
96 | |
77 | |
66 | |
53 |
User | Count |
---|---|
144 | |
104 | |
100 | |
86 | |
64 |