cancel
Showing results for
Did you mean:

The ultimate Microsoft Fabric, Power BI, Azure AI & SQL learning event! Join us in Las Vegas from March 26-28, 2024. Use code MSCUST for a \$100 discount. Register Now

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!

Announcements

#### Microsoft Fabric Community Conference

Join us at our first-ever Microsoft Fabric Community Conference, March 26-28, 2024 in Las Vegas with 100+ sessions by community experts and Microsoft engineering.

#### Power BI Monthly Update - February 2024

Check out the February 2024 Power BI update to learn about new features.

#### Microsoft Fabric Career Hub

Explore career paths and learn resources in Fabric.

#### Microsoft Fabric Partner Community

Engage with the Fabric engineering team, hear of product updates, business opportunities, and resources in the Fabric Partner Community.

Top Solution Authors
Top Kudoed Authors