Accumulate the price per month, taking into account the last date

[Anonymous]

Good afternoon, I'm needing help with the following:

The measurement of test_price in the table on the left is as follows:

test_price =

CALCULATE(CALCULATE(AVERAGE(new_presence_prices[Precios_double] ),LASTDATE(new_presence_prices[created_at]))

,

FILTER ( ALL ( Dates ), Dates[Date] <= MAX ( Dates[Date]))

)

The data in table new_presence_prices is updated only when the price changes, but is always reflected in a line, which generated me that within the evolution chart I could never have the price for each of the months, which in itself was the same.

At the moment what I achieve with this measure is that when it comes to bringing it to the evolution chart, the "uid" that have more than one price or a single price, always respect me the most current in each of the months

The only problem I have is that when within the same month I have more than one "uid" with a price each, I'm always taking the one from the last date, since I'm passing it LASTDATE.

I would like the month 09/2020, for example, to add 6.99 plus 24.95 and then take the average, but since the price of 24.95 is later than 6.99, it only brings me 24.95

[MFelix · Accepted solution]

That's @bi_in_data.

I'm sorry I didn't realize you didn't have values in all your lines, my evil.

Using your measure I remade the average to:

Last_Price = 
var Average_Price = CROSSJOIN (
    SUMMARIZE (
        FILTER(all(new_presence_prices[Created_at];new_presence_prices[UID]); new_presence_prices[Created_at]<= MAX(Dates[Date]));
        new_presence_prices[UID]
                   );
    VALUES ( Dates[Year_Month] )
)
return
AVERAGEX(Average_Price;[ test_price])

Resutl below and in the attachment:

Once again apoligize for confusion about the data.

[Anonymous]

Felix,

I see it works well in your case, but because the table you're using has the data every month.

As I was saying, I have it only when the price changes.

prices = price_double

that's what the table looks like, it would be the uniphacion of those two images.

[MFelix · Accepted solution]

That's @bi_in_data.

I'm sorry I didn't realize you didn't have values in all your lines, my evil.

Using your measure I remade the average to:

Last_Price = 
var Average_Price = CROSSJOIN (
    SUMMARIZE (
        FILTER(all(new_presence_prices[Created_at];new_presence_prices[UID]); new_presence_prices[Created_at]<= MAX(Dates[Date]));
        new_presence_prices[UID]
                   );
    VALUES ( Dates[Year_Month] )
)
return
AVERAGEX(Average_Price;[ test_price])

Resutl below and in the attachment:

Once again apoligize for confusion about the data.

[Anonymous]

Thank you very much, I work excellent, the only thing I do not get to understand well is the averagex, I understand that the first part you do a unification of two tables within a variable, but then within the averagex it is not clear to me how it works.

Thank you!!

[MFelix · Accepted solution]

Don't @bi_in_data,

All X formulas are iterator formulas, which means that the calculation is performed row by row.

In this case what I do with the formula is create a table:

Month - UID - trial price

This creates one row for each month for each UID, then I pick up the average of each row, because in the line chart I only have Month to give contex what happens is that for each month we collect all the lines inside (2 UID in this example) and do the averages of those values:

2020-12 - UID 1 - 6.99

2020-12 - UID 2 - 33.95

Average of previous values:

2020-12 - Both UID - 20.47

This is done for each value of each month.

It also has such calculations for SUMX, COUNTX, COUNTAX, etc.

I hope this helps you understand the process.

[MFelix]

Hi @Anonymous ,

To what I can understand you want to have the latest price value for each UID and then pickup the average of all the ones within the same month and make the average.

Add the following measure:

Average Value = AVERAGEX(VALUES(new_presence_prices[UID]);[test_price])

check result below and in attach PBIX file

[Anonymous]

Felix thank you for the answer,

The problem is that I have the price data as follows.

UID 1

UID 2

With the measure of test_prices I managed to bring the price to each month, but when I analyze two UIDs it happens to me that for example for the month of 9-2020 as the first UID has a created_at of 26/9 and the second UID has only one date, which is 2/06, always brings me the latest, since I am using THE LASTDATE.

I would like it to look as you well explained in the evolution chart, the difference is that the price data I have them in this way that I show you above.

Thank you

[MFelix]

Hi @Anonymous ,

If I understand correctly you want to have the 6.99 of UID 2 consider for the rest of the months and then use that has the average?

I believe that is better for you to use the LASTNONBLANK values to get this calculation, I have made the following measures:

Last_Price = CALCULATE( LASTNONBLANK(new_presence_prices[precious_double];0);ALLSELECTED(Dates[Date]))

Average Value = AVERAGEX(VALUES(new_presence_prices[UID]);[Last_Price])

Has you can see the Last_Price gives the exact same result has your measure:

However when using this on the AVERAGEX what I'm calculating is: 

2020-12 (6.99 + 33.95) / 2 = 20.47

and so on

Is this correct?

Check PBIX file attach.