Forum Discussion
Advocate IRolling average across a Distinct calculation showing incorrect results
Hi MatWebb,
This one will also work
=IF(DATEDIFF(CALCULATE(MIN(DateTable[firstDayOfMonth]),ALLSELECTED(DateTable[firstDayOfMonth])),MAX(DateTable[firstDayOfMonth]),MONTH)<=1,BLANK(),if(HASONEVALUE(DateTable[firstDayOfMonth]),if(ISBLANK([m_Calc_NumberShips_Export]),BLANK(),AVERAGEX(CALCULATETABLE(VALUES(DateTable[firstDayOfMonth]),DATESBETWEEN(DateTable[Date],EDATE(MIN(DateTable[Date]),-2),MAX(DateTable[Date]))),[m_Calc_NumberShips_Export])),BLANK()))
Hope this helps.
Advocate IThanks for your suugested edit v-jiascu-msft but unfortunatly it didnt work - it presented the same answer (it worked as some streamlined dax if nothing else!)
Here's a link to a dummy file setup with the same problem hopefully this will help you spot my error? Ashish_Mathur v-jiascu-msft
https://drive.google.com/file/d/1We8QpuCEjEFlB8yD7IasupOlnkYTIJsG/view?usp=sharing
Thanks so much for helping with this guys, Keeping my fingers crossed
Mat
Super UserHi MatWebb,
This one will also work
=IF(DATEDIFF(CALCULATE(MIN(DateTable[firstDayOfMonth]),ALLSELECTED(DateTable[firstDayOfMonth])),MAX(DateTable[firstDayOfMonth]),MONTH)<=1,BLANK(),if(HASONEVALUE(DateTable[firstDayOfMonth]),if(ISBLANK([m_Calc_NumberShips_Export]),BLANK(),AVERAGEX(CALCULATETABLE(VALUES(DateTable[firstDayOfMonth]),DATESBETWEEN(DateTable[Date],EDATE(MIN(DateTable[Date]),-2),MAX(DateTable[Date]))),[m_Calc_NumberShips_Export])),BLANK()))
Hope this helps.
- MatWebb
Ashish_Mathur you are a Star!
They both worked (which is better than i achieved).
Do you have any comments on which version to use? Im thiking with performance in mind, calculatedtable() feels better than summarize()?Its amazing we needed this beast of a formula to do something quite striaghtforward, any advice/comment on how i could have set this up to make it easier?
thansk again, really appreciate it!
Mat
- Ashish_Mathur
Hi,
You are welcome. Thank you for your kind words. Not sure of which one is better. I think the second one should be.