Forum Discussion
Solved
Rolling average across a Distinct calculation showing incorrect results
Hi Everyone, hopefully this should be a simple one... Some background: my dataset is for a logistics company, where there is one huge fact table (shipments) and many dimentions tables includi...
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.
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
Hi,
You are welcome. Thank you for your kind words. Not sure of which one is better. I think the second one should be.