Forum Discussion

Noivilbo's avatar
Noivilbo
Frequent Visitor
7 years ago
Solved

Measure on different columns

Hi,    I'm making calculations for a business that has a rather recurring revenue model. So I'm trying to calculate the last sale per client, to show the evolution of the underlying revenue base. ...
Need Help
  • v-cherch-msft's avatar
    v-cherch-msft
    7 years ago

    Hi Noivilbo 

    You may create below two measures:

    LastAmountSum =
VAR Lase_date =
    CALCULATE (
        MAX ( Salestable[Date] ),
        FILTER ( ALL ( Datetable ), Datetable[Date] < MAX ( Salestable[Date] ) )
    )
RETURN
    CALCULATE (
        SUM ( Salestable[Amount] ),
        FILTER ( ALL ( Datetable ), Datetable[Date] = Lase_date )
    )
        + CALCULATE ( SUM ( Salestable[Amount] ) )

    Measure =
VAR Max_date =
    CALCULATE (
        MAX ( Salestable[Date] ),
        FILTER ( ALL ( Salestable ), Salestable[Date] <= MAX ( Datetable[Date] ) )
    )
RETURN
    SUMX (
        FILTER ( ALL ( Datetable ), Datetable[Date] = Max_date ),
        [LastAmountSum]
    )

    Regards,

  • v-cherch-msft's avatar
    v-cherch-msft
    7 years ago

    Hi Noivilbo 

    You may check the attached sample file.The formula are measures.If it is not your case,please update the sample data(add the third client) and explain more about how to get your expected output.

    Regards,

v-cherch-msft's avatar
v-cherch-msft
Microsoft Employee
7 years ago

Hi Noivilbo 

You may create below two measures:

LastAmountSum =
VAR Lase_date =
    CALCULATE (
        MAX ( Salestable[Date] ),
        FILTER ( ALL ( Datetable ), Datetable[Date] < MAX ( Salestable[Date] ) )
    )
RETURN
    CALCULATE (
        SUM ( Salestable[Amount] ),
        FILTER ( ALL ( Datetable ), Datetable[Date] = Lase_date )
    )
        + CALCULATE ( SUM ( Salestable[Amount] ) )

Measure =
VAR Max_date =
    CALCULATE (
        MAX ( Salestable[Date] ),
        FILTER ( ALL ( Salestable ), Salestable[Date] <= MAX ( Datetable[Date] ) )
    )
RETURN
    SUMX (
        FILTER ( ALL ( Datetable ), Datetable[Date] = Max_date ),
        [LastAmountSum]
    )

Regards,

Noivilbo's avatar
Noivilbo
Frequent Visitor
7 years ago

Hi, 

 

Thanks for your input; 

I have 2 further questions though, maybe because I don't fully understand the code: 

 

1) Any idea why I get 20 already in the first row, when I use your code - rather than the 5 I would need, and that is included in your printscreen? 

2) It seems that you take the last matching date until the date of the row itself, and manually add the sum of the row itself. However, I would need to end up using the last amount per client (i.e. if I add data for a third client, the code doesn't seem to pick this up. I would expect that somewhere in the code, we would actively work with the "client" column?)

 

Thanks!

 

  • v-cherch-msft's avatar
    v-cherch-msft
    Microsoft Employee
    7 years ago

    Hi Noivilbo 

    You may check the attached sample file.The formula are measures.If it is not your case,please update the sample data(add the third client) and explain more about how to get your expected output.

    Regards,