DAX Function get sales from Clients with sales previous 3 Fiscal Years and no sale This Fiscal Year

AH2022 — Thu, 18 Aug 2022 23:57:16 GMT

Hi All,

I am struggling getting the following DAX function: I need to display the clients and their sales for those clients who have consecutive sales on FiscalYR-3, FiscalYR-2, FiscalYR-1 but not This FiscalYR.

I found this code, but it does not seem to answer. Solved: Re: Calculating Consecutive Years Active - Microsoft Power BI Community

Regards and thanks!

Re: DAX Function get sales from Clients with sales previous 3 Fiscal Years and no sale This Fiscal Year

Jihwan_Kim — Fri, 19 Aug 2022 02:10:20 GMT

Hi,

I am not sure how your dataset looks like, but I tried to create a sample pbix file like below.

Please check the below picture and the attached pbix file. I hope the below can provide some ideas on how to create a slolution for your dataset.

Sales: = SUM( Data[Revenue] )

Customers list expected result: = VAR _currentyear = YEAR ( TODAY () ) VAR _oneyearago = _currentyear - 1 VAR _twoyearsago = _currentyear - 2 VAR _threeyearsago = _currentyear - 3 VAR _currentyearnosalescustomers = SUMMARIZE ( FILTER ( CROSSJOIN ( VALUES ( 'Calendar'[Year] ), VALUES ( Customer[Customer] ) ), 'Calendar'[Year] = _currentyear && [Sales:] = BLANK () ), Customer[Customer] ) VAR _oneyearagocustomerslist = SUMMARIZE ( FILTER ( CROSSJOIN ( VALUES ( 'Calendar'[Year] ), VALUES ( Customer[Customer] ) ), 'Calendar'[Year] = _oneyearago && [Sales:] <> BLANK () ), Customer[Customer] ) VAR _twoyearagocustomerslist = SUMMARIZE ( FILTER ( CROSSJOIN ( VALUES ( 'Calendar'[Year] ), VALUES ( Customer[Customer] ) ), 'Calendar'[Year] = _twoyearsago && [Sales:] <> BLANK () ), Customer[Customer] ) VAR _threeyearagocustomerslist = SUMMARIZE ( FILTER ( CROSSJOIN ( VALUES ( 'Calendar'[Year] ), VALUES ( Customer[Customer] ) ), 'Calendar'[Year] = _threeyearsago && [Sales:] <> BLANK () ), Customer[Customer] ) RETURN CONCATENATEX ( INTERSECT ( INTERSECT ( INTERSECT ( _currentyearnosalescustomers, _oneyearagocustomerslist ), _twoyearagocustomerslist ), _threeyearagocustomerslist ), Customer[Customer], ", " )

Re: DAX Function get sales from Clients with sales previous 3 Fiscal Years and no sale This Fiscal Year

AH2022 — Fri, 19 Aug 2022 18:16:48 GMT

Perfect! I really appreciate!

Thank you Jihwan_Kim

topic DAX Function get sales from Clients with sales previous 3 Fiscal Years and no sale This Fiscal Year in DAX Commands and Tips

DAX Function get sales from Clients with sales previous 3 Fiscal Years and no sale This Fiscal Year

Re: DAX Function get sales from Clients with sales previous 3 Fiscal Years and no sale This Fiscal Year

Re: DAX Function get sales from Clients with sales previous 3 Fiscal Years and no sale This Fiscal Year