Forum Discussion

MatWebb's avatar
MatWebb
Advocate I
8 years ago
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...
Need Help
  • Ashish_Mathur's avatar
    Ashish_Mathur
    8 years ago

    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's avatar
Ashish_Mathur
Super User
8 years ago

Hi MatWebb,

 

Try this measure for computnig the rolling three month average

 

=CALCULATE(AVERAGE([No. Unique vehicles]),DATESBETWEEN(DateTable[Month/Year],EDATE(MIN(DateTable[Month/Year]),-2),MAX(DateTable[Month/Year])))

 

Hope this helps.

  • MatWebb's avatar
    MatWebb
    Advocate I
    8 years ago

    Thanks for Replying with a suggestion Ashish_Mathur, unfortunatly this doesn't work as dont have a 'unique number of vehicles' column. I can create a measure for this (Distinctcount() ) but then that cant go on the Average() formula.

     

    Have i missed something?

     

    thanks in advance

    • Ashish_Mathur's avatar
      Ashish_Mathur
      Super User
      8 years ago

      Hi,

       

      Please share the link from where i can download your PBI file.