cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
EvertonRamone Regular Visitor
Regular Visitor

Number of patients whose lost 5% of weight

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

 

 

DatePatientProgramWeight
01/01/2018MariaA60
02/01/2018JohnnyA70
03/01/2018CarlosA50
01/02/2018MariaB60
02/03/2018JohnnyD70
03/04/2018CarlosG50
01/06/2018MariaF56
02/07/2018JohnnyF66
03/06/2018CarlosH50

 

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?

 

 

 

 

6 REPLIES 6
Super User
Super User

Re: Number of patients whose lost 5% of weight

@EvertonRamone

 

In your sample The count will be 0?

 

Maria 60 - 58 => 3.3%

Johnny 70 - 69 = 1.4%

Carlos  50 - 50 = 0.0%

 

 




Lima - Peru
Did I answer your question? Mark my post as a solution!

Proud to be a Datanaut!




EvertonRamone Regular Visitor
Regular Visitor

Re: Number of patients whose lost 5% of weight

Hi, edited.

Super User
Super User

Re: Number of patients whose lost 5% of weight

@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




Lima - Peru
Did I answer your question? Mark my post as a solution!

Proud to be a Datanaut!




EvertonRamone Regular Visitor
Regular Visitor

Re: Number of patients whose lost 5% of weight

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?

Super User
Super User

Re: Number of patients whose lost 5% of weight

@EvertonRamone

 

The formula should be work with month 7 also.

 

I tested with your data sample

 

Regards

 

Victor 




Lima - Peru
Did I answer your question? Mark my post as a solution!

Proud to be a Datanaut!




EvertonRamone Regular Visitor
Regular Visitor

Re: Number of patients whose lost 5% of weight

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