Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Enhance your career with this limited time 50% discount on Fabric and Power BI exams. Ends August 31st. Request your voucher.

Reply
AH2022
Helper II
Helper II

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

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!

1 ACCEPTED SOLUTION
Jihwan_Kim
Super User
Super User

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.

 

Picture1.png

 

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

If this post helps, then please consider accepting it as the solution to help other members find it faster, and give a big thumbs up.


Click here to visit my LinkedIn page

Click here to schedule a short Teams meeting to discuss your question.

View solution in original post

2 REPLIES 2
Jihwan_Kim
Super User
Super User

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.

 

Picture1.png

 

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

If this post helps, then please consider accepting it as the solution to help other members find it faster, and give a big thumbs up.


Click here to visit my LinkedIn page

Click here to schedule a short Teams meeting to discuss your question.

Perfect! really appreciate!

Thank you @Jihwan_Kim 

Helpful resources

Announcements
August Power BI Update Carousel

Power BI Monthly Update - August 2025

Check out the August 2025 Power BI update to learn about new features.

August 2025 community update carousel

Fabric Community Update - August 2025

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