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.
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
      )
   )

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,

3 Replies

  • 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,