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.

v-jiascu-msft's avatar
v-jiascu-msft
Microsoft Employee
8 years ago

Hi MatWebb,

 

I made some changes of your formula. Please try it out. But I don't know if it's correct. Please share a dummy pbix file.

m_rAvg12_NoVehicles_Month =
VAR month = 3
RETURN
    DIVIDE (
        CALCULATE (
            DISTINCTCOUNT ( Shipments[ImoNo] ),
            //distinct count on the vehicle id
            FILTER (
                PlacesD,
                NOT PlacesD[CountryCodeD] IN DISTINCT ( PlacesL[CountryCode] )
            ),
            DATESINPERIOD ( 'datetable'[date], MAX ( 'datetable'[date] ), - month, MONTH )
        ),
        month
    )

Best Regards!

Dale

  • MatWebb's avatar
    MatWebb
    Advocate I
    8 years ago

    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

    • Ashish_Mathur's avatar
      Ashish_Mathur
      Super User
      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.

      • MatWebb's avatar
        MatWebb
        Advocate I
        8 years ago

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

      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.