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!

Announcements

#### Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

If you love stickers, then you will definitely want to check out our Community Sticker Challenge!

#### Power BI Monthly Update - July 2024

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

#### Fabric Community Update - July 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors
Top Kudoed Authors