Rolling 6 Month Count

[Anonymous]

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 · Accepted solution]

@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]

Thank you, Looks like this works! 

[PaulDBrown]

@Anonymous 

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

[Anonymous]

I'm using this 

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

[amitchandak]

@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]

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]

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 

[pranit828]

HI @Anonymous 

CALCULATE(DISTINCTCOUNT(Sales[Sales Amount]),DATESMTD(dateadd('Date'[Date],-6,MONTH)))

[pranit828]

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]

Thank you @pranit828  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