cancel
Showing results for
Did you mean:

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Frequent Visitor

## Look at last 14 months and include in Table or not

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 ?

1 ACCEPTED SOLUTION
Helper I

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)
2 REPLIES 2
Helper I

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

Do you have some example data without sensitve data?

Proud to be a Super User!