Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
7 years ago
Solved

Calculating Patient Weight Loss between First and last date

Hi,

I am very new to all things Power BI. So any help and patience is appreciated. 

I am trying to calculate the weight loss for my clients between first and last visits. 

I have a table that contains patient ID, weight, test date. 

IDWeightTest Date
31017131Wednesday, October 18,2017
31017136Wednesday, September 20, 2017

Is a 'New Measure' the best way to do this?

Thanks in advance. 

Need Help
Tips and Tricks
Tutorial Requests
  • v-frfei-msft's avatar
    v-frfei-msft
    7 years ago

    Hi Anonymous ,

     

    If you have multi IDs, then we can create a measure as below.

     

     

    Measure = 
VAR mind =
    MIN ( 'Table'[Test Date] )
VAR maxd =
    MAX ( 'Table'[Test Date] )
VAR maxv =
    CALCULATE (
        SUM ( 'Table'[Weight] ),
        FILTER ( 'Table', 'Table'[Test Date] = maxd )
    )
VAR minv =
    CALCULATE (
        SUM ( 'Table'[Weight] ),
        FILTER ( 'Table', 'Table'[Test Date] = mind )
    )
RETURN
    maxv - minv

     

     

2 Replies

  • Zubair_Muhammad's avatar
    Zubair_Muhammad
    Icon for Community Champion rankCommunity Champion
    7 years ago

    Anonymous 

     

    Try this MEASURE

     

    Measure =
VAR Start_ =
    MIN ( Table1[Test Date] )
VAR End_ =
    MAX ( Table1[Test Date] )
RETURN
    CALCULATE ( MAX ( Table1[Weight] ), Table1[Test Date] = End_ )
        - CALCULATE ( MAX ( Table1[Weight] ), Table1[Test Date] = Start_ )
  • v-frfei-msft's avatar
    v-frfei-msft
    Icon for Community Support rankCommunity Support
    7 years ago

    Hi Anonymous ,

     

    If you have multi IDs, then we can create a measure as below.

     

     

    Measure = 
VAR mind =
    MIN ( 'Table'[Test Date] )
VAR maxd =
    MAX ( 'Table'[Test Date] )
VAR maxv =
    CALCULATE (
        SUM ( 'Table'[Weight] ),
        FILTER ( 'Table', 'Table'[Test Date] = maxd )
    )
VAR minv =
    CALCULATE (
        SUM ( 'Table'[Weight] ),
        FILTER ( 'Table', 'Table'[Test Date] = mind )
    )
RETURN
    maxv - minv