COUNTROWS for warranty exposure counts

[cec71890]

I need a formular for showing warranty exposure (historical and current).Here is what we have now: 

Rolling Exposure= CALCULATE(DISTINCTCOUNT(warrexposure[serialnumber]),DATESBETWEEN(calendar[date], STARTOFMONTH(calendar[date]),DATEADD(STARTOFMONTH(calendar[date]),10,YEAR)))) 

This formula is providing an accurate current exposure but the historical is inaccurate because it’s backfilling warranty start dates from future months into previous months because not all of the warranties last 10 full years. If that makes sense to anyone- please help ☺️ And just for clarification- when I say exposure, I mean we want to see a line chart showing the number of serial numbers under warranty on the first day of the month over 24 months. Our warranties last 18mo, 5 years, and 10 years.

[v-zhangti]

Hi, @cec71890 

Can you provide some sample data or simple pbix files? Sensitive information can be removed in advance. What kind of expected results do you expect? You can also show it with pictures.

Best Regards

[cec71890]

This solution is close but only displays dates that fall w/in 12 or 18 months. Basically, I need to capture all the warranty start dates from STARTOFMONTH and end date is in the future (some will be 5 years, some will be 10 years.. etc..)

[amitchandak]

@cec71890 , with help from calendar/date table try like

Rolling 12 = CALCULATE(DISTINCTCOUNT(warrexposure[serialnumber]),DATESINPERIOD('Date'[Date ],MAX('Date'[Date ]),-12,MONTH))

or

Rolling 18 = CALCULATE(DISTINCTCOUNT(warrexposure[serialnumber]),DATESINPERIOD('Date'[Date ],MAX('Date'[Date ]),-18,MONTH))