The ultimate Fabric, Power BI, SQL, and AI community-led learning event. Save €200 with code FABCOMM.
Get registeredEnhance your career with this limited time 50% discount on Fabric and Power BI exams. Ends August 31st. Request your voucher.
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!
Solved! Go to 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],
", "
)
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],
", "
)
User | Count |
---|---|
11 | |
8 | |
6 | |
6 | |
6 |
User | Count |
---|---|
23 | |
14 | |
13 | |
10 | |
8 |