Starting December 3, join live sessions with database experts and the Microsoft product team to learn just how easy it is to get started
Learn moreGet certified in Microsoft Fabric—for free! For a limited time, get a free DP-600 exam voucher to use by the end of 2024. Register now
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
Solved! Go to 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
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
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
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.
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
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
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...
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
This link has the DAX Measure in it.
https://drive.google.com/file/d/1Qc6dQnkbNHkwAZeoh5eCdeI78JUDblmz/view?usp=sharing
Hello:
This can give 27 as answer:
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.
If price is column
averageX(allselected(Table), Table[price])
if price is a measure
calculate(averageX(values(Table[Date], [price]), allselected())
Starting December 3, join live sessions with database experts and the Fabric product team to learn just how easy it is to get started.
March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Early Bird pricing ends December 9th.
User | Count |
---|---|
90 | |
87 | |
84 | |
66 | |
49 |
User | Count |
---|---|
131 | |
110 | |
96 | |
70 | |
67 |