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.
Thanks 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
Hi MatWebb,
This calculated field formula will 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(SUMMARIZE(DateTable,DateTable[firstDayOfMonth],"ABCD",[m_Calc_NumberShips_Export]),DATESBETWEEN(DateTable[Date],EDATE(MIN(DateTable[Date]),-2),MAX(DateTable[Date]))),[ABCD])),BLANK()))
Hope this helps.