Number of patients whose lost 5% of weight

[EvertonRamone]

I need a dax measure wich has this rules below:

Count of patients whose lost 5% of weight in 6 months*

1 - First the patients answer a question about their weight in the program "A" (This is considerated the first date of weight)

2 -Then, a lot of times they measure their weights

3 - I want to know their results after 6 months

4 - If they didn't measure their weight after 6 months, I want to see the result after 7 months (DON'T Matter the type of program)

5 - Now, only the count of those whose lost 5% of weight

Date	Patient	Program	Weight
01/01/2018	Maria	A	60
02/01/2018	Johnny	A	70
03/01/2018	Carlos	A	50
01/02/2018	Maria	B	60
02/03/2018	Johnny	D	70
03/04/2018	Carlos	G	50
01/06/2018	Maria	F	56
02/07/2018	Johnny	F	66
03/06/2018	Carlos	H	50

My count should be returned as 2, because Maria and Johnny lost >= 5% of weigth. 

PS: I count Jonny, because I'd search for him as I didn't find him after 6 months

Can someone help me?

[Vvelarde]

@EvertonRamone

In your sample The count will be 0?

Maria 60 - 58 => 3.3%

Johnny 70 - 69 = 1.4%

Carlos  50 - 50 = 0.0%

[EvertonRamone]

Hi, edited.

[Vvelarde]

@EvertonRamone

Hi, you can use 2 measures:

%Change =
VAR _FirstDate =
    CALCULATE ( FIRSTDATE ( Table1[Date] ), Table1[Program] = "A" )
VAR _WeightinFirstDate =
    CALCULATE ( VALUES ( Table1[Weight] ), Table1[Date] = _FirstDate )
VAR _6monthslater =
    CALCULATE (
        FIRSTDATE ( Table1[Date] ),
        Table1,
        Table1[Date] >= EDATE ( _FirstDate, 5 )
    )
VAR _Weightafter6months =
    CALCULATE ( VALUES ( Table1[Weight] ), Table1[Date] = _6monthslater )
VAR _PercentOfChange =
    DIVIDE ( ( _Weightafter6Months - _WeightinFirstDate ), _Weightafter6Months )
RETURN
    _PercentOfChange

CountLostWeight =
SUMX ( VALUES ( Table1[Patient] ), IF ( [%Change] <= -0.05, 1, 0 ) )

Regards

Victor

[EvertonRamone]

Thanks Victor. Helped me a lot.

I will mark your answer as solution.

Just another doubt: Can I check in month 7 if I don't find the patient in month 6? How would I do this?

[Vvelarde]

@EvertonRamone

The formula should be work with month 7 also.

I tested with your data sample

Regards

Victor 

[EvertonRamone]

The measure is looking foward 6 months after.

01/01/2018 will check 01/06/2018, but i'd like to see until the whole month (last day of month)

I don't want to see exactly six months and days later, but a range of that month taking the min date if the weight ocurred more than once