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

[AH2022]

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!

[Jihwan_Kim · Accepted solution]

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],
        ", "
    )

[AH2022]

Perfect! I really appreciate!

Thank you @Jihwan_Kim