Forum Discussion

PBIX_COACH's avatar
PBIX_COACH
Icon for Helper II rankHelper II
3 years ago
Solved

Customer Count by Active Years

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 t...
  • tamerj1's avatar
    tamerj1
    3 years ago

    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's avatar
tamerj1
Icon for Community Champion rankCommunity Champion
3 years ago

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] )
)
  • PBIX_COACH's avatar
    PBIX_COACH
    Icon for Helper II rankHelper II
    3 years ago

    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.

  • PBIX_COACH's avatar
    PBIX_COACH
    Icon for Helper II rankHelper II
    3 years ago

    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?

    • tamerj1's avatar
      tamerj1
      Icon for Community Champion rankCommunity Champion
      3 years ago

      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 ) ) ) )
)