Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
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
User | Count |
---|---|
127 | |
108 | |
99 | |
63 | |
62 |
User | Count |
---|---|
136 | |
113 | |
102 | |
71 | |
60 |