cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Noivilbo Frequent Visitor
Frequent Visitor

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!!

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

2 ACCEPTED SOLUTIONS

Accepted Solutions
v-cherch-msft Super Contributor
Super Contributor

Re: Measure on different columns

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

1.png

Regards,

Community Support Team _ Cherie Chen
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

View solution in original post

v-cherch-msft Super Contributor
Super Contributor

Re: Measure on different columns

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.

1.png

Regards,

Community Support Team _ Cherie Chen
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

View solution in original post

3 REPLIES 3
v-cherch-msft Super Contributor
Super Contributor

Re: Measure on different columns

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

1.png

Regards,

Community Support Team _ Cherie Chen
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

View solution in original post

Noivilbo Frequent Visitor
Frequent Visitor

Re: Measure on different columns

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!

 

Untitled.png

v-cherch-msft Super Contributor
Super Contributor

Re: Measure on different columns

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.

1.png

Regards,

Community Support Team _ Cherie Chen
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

View solution in original post

Helpful resources

Announcements
Ask Amir Anything

Exclusive LIVE Community Event No. 2 – Ask Amir Anything

Next in our Triple A series: Ask Amir Netz questions about the latest updates, features and future.

October 2019 Community Highlights

October 2019 Community Highlights

October was a busy month in the community. Read the recap article to learn about some of the events and content.

New Solution Badges

New Solution Badges

Two waves of brand new solution badges are coming! Read the article for more information on our new community badges.

Analytics in Azure virtual event

Analytics in Azure virtual event

Experience a limitless analytics service built to ingest, prep, manage, and serve data for immediate use in Power BI.

Users Online
Currently online: 339 members 3,110 guests
Please welcome our newest community members: