topic Look at last 14 months and include in Table or not in DAX Commands and Tips

Look at last 14 months and include in Table or not

JimmyKhan2022 — Mon, 27 Feb 2023 18:21:16 GMT

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 ?

Re: Look at last 14 months and include in Table or not

andhiii079845 — Mon, 27 Feb 2023 19:07:35 GMT

Do you have some example data without sensitve data?

Re: Look at last 14 months and include in Table or not

Anonymous — Mon, 27 Feb 2023 19:35:21 GMT

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)