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 ...

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 ))

Forum Discussion

Rolling 6 Month Count