calculate average from table with date wise price changes

[nileshpca]

to keep things simple, my table has date and price

date of price change | price

01-Jan-21 | 22

04-Jan-21 | 20

16-Jan-21 | 18

22-Jan-21 | 21

average will calculate simple average of the four prices. i want dax formula to calculate average for the period considering the price for each day

date | price

01-Jan-21 | 22

02-Jan-21 | 22

03-Jan-21 | 22

04-Jan-21 | 20

04-Jan-21 | 20

...

tried experimenting with lastnonblank but no success. please help

[v-yangliu-msft · Accepted solution]

Hi  @nileshpca ,

Here are the steps you can follow：

1. Create measure.

table1_sum =
var _summtable=
SUMMARIZE('Table 2','Table 2'[all date],'Table 2'[price],
"price_change",
var _1=CALCULATE(SUM('Table 2'[price]),FILTER(ALL('Table 2'),'Table 2'[all date]=EARLIER('Table 2'[all date])))
var _2=CALCULATE(SUM('Table 2'[price]),FILTER(ALL('Table 2'),'Table 2'[all date]=EARLIER('Table 2'[all date])-1))
return
IF(
    _1<> _2,MINX(FILTER(ALL('Table 2'),'Table 2'[price]=EARLIER('Table 2'[price])),[price]),BLANK()),
"count",COUNTX(FILTER(ALL('Table 2'),'Table 2'[price]=EARLIER('Table 2'[price])),[all date]),
"count_all",COUNTX(ALL('Table 2'),[all date]),
"sum_all",SUMX(ALL('Table 2'),[price]))
return
DIVIDE( SUMX(_summtable,[sum_all]),SUMX(_summtable,[count_all]))

2. Result:

Best Regards,

Liu Yang

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly

[nileshpca]

thanks.. this works perfect

[nileshpca]

hi,

thanks...

it is giving simple average of the four prices. i want to consider the price for all the days of the month. if i am not clear, uploading sample file with solution required..

https://drive.google.com/file/d/15u4fZG4Y2IGHWnGv9G78R6i_OwTXuSXL/view?usp=sharing

nilesh

[Whitewater100]

Hi:

Here is the example file..

https://drive.google.com/file/d/1Qc6dQnkbNHkwAZeoh5eCdeI78JUDblmz/view?usp=sharing 

[nileshpca]

this works great.. but how do i use this if there are 50 products across 6 years.. will have to make price with all dates for 6 years for each product. Can there be a dax solution without calculated column... I tried but it seems EARLIER cannot be used in measure.

[Whitewater100]

Yes, I see. You need to merge table 1 into Table 2 (on date) and choose Fill Down for the new column with only four entries with the transform tab. Then you can apply your regular average formula to this new column(Price1).

[nileshpca]

hi,

thanks.. was looking for a dax measure instead of power query solution since there are lot of products involved. example was given for one product to keep it simple

[Whitewater100]

Hi:

I have created another way to get to yout initial question.

Power Query(example#1) is the best for lot's of items and changes.

So far we have three different solutions to your original question. 

If you want to post a new question with new data.

Here is the file link with the various solutions.

Thanks!https://drive.google.com/file/d/1Qc6dQnkbNHkwAZeoh5eCdeI78JUDblmz/view?usp=sharing 

[nileshpca]

thanks a lot. i am using powerquery currently, but only for 2-3 products. there are 50 + products over 6 years. the pq table will be huge drain on memory.

was thinking is it possible to create virtual table in dax with date column from calender and product selected from filter row and then fill down in the virtual table.

that would be awesome, if it can happen...

[Whitewater100]

Hi:

I beleive if you have an additional date table (like your table 2)and run the PQ process I mentioned (and DAX sum measure after) it will be a faster solution pushing the procesing back to the query editor. Pushing back closer to source is generally best practice.

That said, here is new update to file. You just need to have your table with the four prices in it filled out with all the dates for the month. I called it "Price Table". There is DAX Calc Col.

I hope this is the solution to work for you!https://drive.google.com/file/d/1Qc6dQnkbNHkwAZeoh5eCdeI78JUDblmz/view?usp=sharing 

[Whitewater100]

This link has the DAX Measure in it.

New Avg Price = AVERAGE(PriceTable[New Price Fill Down])

https://drive.google.com/file/d/1Qc6dQnkbNHkwAZeoh5eCdeI78JUDblmz/view?usp=sharing 

[Whitewater100]

Hello:

This can give 27 as answer:

Avg Price II =

DIVIDE(SUM('Table 2'[Price]),

COUNTROWS('Table 2'))

Is this the measure you want? Thanks..

[nileshpca]

i want the answer 27 from Table1. Table2 is not in the actual database and is to show how the average is to be calculated.

[amitchandak]

@nileshpca ,

If price is column

averageX(allselected(Table), Table[price])

if price is a measure
calculate(averageX(values(Table[Date], [price]), allselected())