Measure on different columns

[Noivilbo]

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.
I've added a simple example table below, showing the 2 tables, and the orange column that I'd like to use in a visual.

2 questions:

1) How can I obtain the orange column below? (See my first attempts at the bottom of this post)
2) Can I do this in a measure? (I don't want to pollute my date table with calculations)

Link to sample data: https://www.dropbox.com/s/780chlil58x8l46/Test.pbix?dl=0

Thanks!!

FYI - What I got so far, gives me the latest entry in a calculated column - I just don't know how to get this per client;

MAX_YTD =
   VAR Max_Date =
   CALCULATE(
      max(Salestable[Date]);
      FILTER(
         Salestable;
         Salestable[Date]<=Datetable[Date]
      )
   )   

RETURN
   CALCULATE(
      SUM(Salestable[Amount]);
      FILTER(
         FILTER(
            All(Salestable);
            Salestable[Date]<=Datetable[Date]
         );
      Salestable[Date]=Max_Date
      )
   )

[v-cherch-msft · Accepted solution]

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 · Accepted solution]

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]

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 · Accepted solution]

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,