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.
Hello Team I need some assistance getting a DAX formula to work for this. I have two tables (Customers sales table and Calendar table) connected by the date field and I would like to get a count of the total active years for that customer.
Any assistance would be greatly appreciated.
Thanks
Solved! Go to Solution.
Hi @PBIX_COACH
You can use the matrix total column to present the Total Active Years (You can simply rename it). You need to replace the current measure (Measure1) with the following
Measure2 =
IF (
ISINSCOPE ( 'Date'[Year] ),
[Measure1],
COUNTX ( VALUES ( 'Date'[Year] ), [Measure1] )
)
Hi @PBIX_COACH
You can use the matrix total column to present the Total Active Years (You can simply rename it). You need to replace the current measure (Measure1) with the following
Measure2 =
IF (
ISINSCOPE ( 'Date'[Year] ),
[Measure1],
COUNTX ( VALUES ( 'Date'[Year] ), [Measure1] )
)
@tamerj1 on another note how can I flag if there was a "win back" for example company A had a gap year in 2021 but returned in 2022?
@PBIX_COACH
Please try
Measure2 =
IF (
ISINSCOPE ( 'Date'[Year] ),
[Measure1],
VAR T1 =
ADDCOLUMNS ( VALUES ( 'Date'[Year] ), "@Value", [Measure1] )
VAR T2 =
FILTER ( T1, [@Value] <> BLANK () )
RETURN
"Total Active Years: " & COUNTROWS ( T2 )
& UNICHAR ( 10 ) & "Win Back Years: "
& SUMX ( T2, INT ( ISEMPTY ( FILTER ( T2, [Year] = EARLIER ( [Year] ) - 1 ) ) ) )
)
thanks for the assist @tamerj1 but it's returning the total count for all companies by the year but I'm looking for the unique count of years. for example, company A has an activity for 3 years because it has data in 2019, 2020 and 2022 so it should return a total of 3 years active.
User | Count |
---|---|
26 | |
12 | |
8 | |
7 | |
5 |
User | Count |
---|---|
28 | |
13 | |
12 | |
12 | |
6 |