Look at last 14 months and include in Table or not

[JimmyKhan2022]

I have a page where I select the month and it display the Revenue for that Financial Month. Now the client want the following to determine commercially dead customers .

"If 14 consecutive months prior to Selected Month and including the selected month, there is 0 revenue each month - Then Include in Table"

Figure 1 - Formula for standard Revenue for the month

Revenue USD =

VAR ReferenceDate = MAX('Time'[Financial_Month] )
VAR _Revenue_USD = SUM ( 'FinanceData'[usd_revenue] )

RETURN IF ( _Revenue_USD>=0 && ReferenceDate="Current Month", _Revenue_USD, _Revenue_USD)

Is there a simple DAX way to do this ?

[Anonymous · Accepted solution]

first calculate revenue for last 14 months

var daterange = LASTDATE( ( 'Date Table'[Date])

VAR _Revenue_USD = SUM ( 'FinanceData'[usd_revenue]),

DATESINPERIOD ( 'Date Table'[Date], dateRange, -14,MONTH)

)

then you can apply if condition

if(_Revenue_USD= 0 ,_Revenue_USD)

[andhiii079845]

Do you have some example data without sensitve data?