Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
6 years ago
Solved

Rolling 6 Month Count

Hello,

 

I want to calculate the Distinct Count for Rolling 6 months for the ORDER_NO

For example: If I'm looking at the current month, the Rolling 6Month Count should reflect the total count for the prior 6 months (not including the current month)

Here is what I tried but it does not give me the correct numbers.

 

Rolling 6M Count = CALCULATE(DISTINCTCOUNT(Table[ORDER_NO]),
DATESINPERIOD('Calendar'[Date], MAX(Table[TA1]), -6,MONTH ))
 
(I do have a Calendar Table which is linked  to TA1)

 

 

Any help on this much appreciated! Thank you.

  • PaulDBrown's avatar
    PaulDBrown
    6 years ago

    Anonymous 

    How about:

     

     

     

    Rolling 6M Count = CALCULATE(DISTINCTCOUNT(Table[ORDER_NO]),
DATESINPERIOD('Calendar'[Date], MAX('Calendar [Date]), -6,MONTH )) - DISTINCTCOUNT(Table[ORDER NO])

     

     

     

    This should exclude the current month value. 

    EDIT: Actually, depending on whether you wish to exclude the rolling last month, you might need:

     

    Rolling 6M Count = CALCULATE(DISTINCTCOUNT(Table[ORDER_NO]),
DATESINPERIOD('Calendar'[Date], MAX('Calendar [Date]), -7,MONTH )) - CALCULATE(DISTINCTCOUNT(Table[ORDER NO]), DATESINPERIOD(Calendar'[Date], MAX('Calendar [Date]), -1,MONTH ))

     

10 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    6 years ago

    HI Anonymous 

    CALCULATE(DISTINCTCOUNT(Sales[Sales Amount]),DATESMTD(dateadd('Date'[Date],-6,MONTH)))
    • Anonymous's avatar
      Anonymous
      Not applicable
      6 years ago

      HI Anonymous 

      I tested it out, you could refer to below steps:

      Create a calender table:

      Table = CALENDARAUTO()

      Create a measure:

      DIVIDE(CALCULATE(DISTINCTCOUNT(Sales[Sales Amount]),DATESINPERIOD('Table'[Date],MAX('Sales'[Date]),-6,MONTH)),6)
    • Anonymous's avatar
      Anonymous
      Not applicable
      6 years ago

      Thank you Anonymous  it did not work,  it gave me the count for the 6th month ago Order only. 

      For example: for July the result was 6 (Jan only count), It should be Order numbers count from Jan -Jun) 

       

      Thanks 

  • PaulDBrown's avatar
    PaulDBrown
    Icon for Community Champion rankCommunity Champion
    6 years ago

    Anonymous 

    How about:

     

     

     

    Rolling 6M Count = CALCULATE(DISTINCTCOUNT(Table[ORDER_NO]),
DATESINPERIOD('Calendar'[Date], MAX('Calendar [Date]), -6,MONTH )) - DISTINCTCOUNT(Table[ORDER NO])

     

     

     

    This should exclude the current month value. 

    EDIT: Actually, depending on whether you wish to exclude the rolling last month, you might need:

     

    Rolling 6M Count = CALCULATE(DISTINCTCOUNT(Table[ORDER_NO]),
DATESINPERIOD('Calendar'[Date], MAX('Calendar [Date]), -7,MONTH )) - CALCULATE(DISTINCTCOUNT(Table[ORDER NO]), DATESINPERIOD(Calendar'[Date], MAX('Calendar [Date]), -1,MONTH ))

     

    • Anonymous's avatar
      Anonymous
      Not applicable
      6 years ago

      Thank you, Looks like this works! 

      • PaulDBrown's avatar
        PaulDBrown
        Icon for Community Champion rankCommunity Champion
        6 years ago

        Anonymous 

        Great! Out of curiosity, which one are you using?

  • amitchandak's avatar
    amitchandak
    Icon for Super User rankSuper User
    6 years ago

    Anonymous , try like

     

    Rolling 6M Count = CALCULATE(DISTINCTCOUNT(Table[ORDER_NO]),
    DATESINPERIOD('Calendar'[Date],eomonth( MAX(Table[TA1]),-1), -6,MONTH ))

     

    Rolling 6M Count = CALCULATE(DISTINCTCOUNT(Table[ORDER_NO]),
    DATESINPERIOD('Calendar'[Date],ENDOFMONTH( MAX(Table[TA1]),-1,month), -6,MONTH ))

    • Anonymous's avatar
      Anonymous
      Not applicable
      6 years ago

      Hi amitchandak Thank you, but it didn't give me the results I'm looking for. 

       

      I used the following

      Rolling 6M Count = CALCULATE(DISTINCTCOUNT(Table[ORDER_NO]),
      DATESINPERIOD('Calendar'[Date],eomonth( MAX(Table[TA1]),-1), -6,MONTH ))

       

      The results I'm getting starts from Feb 19, My data starts from Jan 19 to current Date. 

      The Rolling 6 Month count should start to show from the 6th month correct? 

      For example, If I'm looking at July 19, It should show the total count of Orders from Jan 19 -Jun 19. 

       

      Jan 19 -Jun 19 Should not have any results since there are no data for the previous 6 months data available. 

       

      Thank you!

      • Anonymous's avatar
        Anonymous
        Not applicable
        6 years ago

        I have a different Measure which counts the order numbers for Rolling months.  which works great. Is there a way to use that Measure to calculate this 6 Month rolling?

         

        Rolling 6M Count = CALCULATE([Rolling Pass Count]) ................?
         
        Thanks