Forum Discussion
calculate average from table with date wise price changes
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
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
14 Replies
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
thanks.. this works perfect
- amitchandak
Super User
If price is column
averageX(allselected(Table), Table[price])
if price is a measure
calculate(averageX(values(Table[Date], [price]), allselected()) 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
Whitewater100Solution Sage
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..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.
- 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).
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:
Here is the example file..
https://drive.google.com/file/d/1Qc6dQnkbNHkwAZeoh5eCdeI78JUDblmz/view?usp=sharing
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.
