Forum Discussion

Syndicate_Admin's avatar
Syndicate_Admin
Icon for Administrator rankAdministrator
5 years ago
Solved

How I accumulate clients different months

Hello Community, Good day, someone can give me a light with the next problem in power BI. Example: I have a list of clients in the course of the year where a client can be served more than once i...
  • PaulDBrown's avatar
    PaulDBrown
    5 years ago

    Ok. With this model:

    And these measures:

    Number of attentions = COUNT(FactTable[CUSTOMER])
    Distinct Customers = DISTINCTCOUNT(FactTable[CUSTOMER])

    and..

    New customers =
CALCULATE (
    DISTINCTCOUNT ( FactTable[CUSTOMER] ),
    FILTER (
        ALL ( 'Date Table' ),
        'Date Table'[Date] <= MAX ( 'Date Table'[Date] )
    )
)

     

    You get the following:

     

     

  • PaulDBrown's avatar
    PaulDBrown
    5 years ago

    This should work:

    New customers =
VAR _Year = SELECTEDVALUE ( Date Table [Year] )
CALCULATE (
    DISTINCTCOUNT ( FactTable[CUSTOMER] ),
    FILTER (
        ALL ( 'Date Table' ),
        'Date Table'[Date] <= MAX ( 'Date Table'[Date] )
        && Date Table [Year] = _Year
    )
)
PaulDBrown's avatar
PaulDBrown
Icon for Community Champion rankCommunity Champion
5 years ago

Sorry, could you please explain the relevance of the rows where Date = "21 years ago"?

Also, do the date values such as 21-feb or 21-july refer to year-month or date-month?

what do the rows for  Date = June 21-2 actually mean? (does your real data actually look like this??)

It would be very helpful is you provided a sample PBIX file via Dropbox, OneDrive, Google Drive...

Syndicate_Admin's avatar
Syndicate_Admin
Icon for Administrator rankAdministrator
5 years ago

Sorry, the table was copied wrong when pasting it, the date field is in date format (DD/MM/YYYY) but pasting it here changes the format.

DATECUSTOMER
January/2021KAREN
January/2021KAREN
January/2021PAOLA
January/2021VAIN
February/2021PAOLA
February/2021ANDREINA (NOWA)
February/2021VAIN
February/2021GABRIELA
March/2021KARINA
March/2021PAOLA
March/2021ANDREINA (NOWA)
March/2021KAREN
March/2021ANGELA
April/2021CARLA
April/2021YENNY
April/2021ADELAIDE
April/2021PAOLA
May/2021SOFIA
May/2021GIULIANNA
May/2021ANDREINA (NOWA)
June/2021PAOLA
June/2021ANGELA
June/2021ROSE
July/2021CYNTHIA
July/2021CYNTHIA
July/2021PAULA
July/2021PRISCILLA
August/2021KAREN
August/2021ANGELA
August/2021ANGELA
August/2021PAULETTE
UNIQUE PATIENTS17

  • PaulDBrown's avatar
    PaulDBrown
    Icon for Community Champion rankCommunity Champion
    5 years ago

    Ok. With this model:

    And these measures:

    Number of attentions = COUNT(FactTable[CUSTOMER])
    Distinct Customers = DISTINCTCOUNT(FactTable[CUSTOMER])

    and..

    New customers =
CALCULATE (
    DISTINCTCOUNT ( FactTable[CUSTOMER] ),
    FILTER (
        ALL ( 'Date Table' ),
        'Date Table'[Date] <= MAX ( 'Date Table'[Date] )
    )
)

     

    You get the following:

     

     

    • Syndicate_Admin's avatar
      Syndicate_Admin
      Icon for Administrator rankAdministrator
      5 years ago

      Thank you very much, there is only something that does not come out, it does not restart in each year, it accumulates for all the years that there is customer service.

      KrnVelez1_0-1630602611844.pngKrnVelez1_1-1630602662364.png


      What would be the modification so that the beginning of each year is restarted?

      Thanks a lot

      • PaulDBrown's avatar
        PaulDBrown
        Icon for Community Champion rankCommunity Champion
        5 years ago

        This should work:

        New customers =
VAR _Year = SELECTEDVALUE ( Date Table [Year] )
CALCULATE (
    DISTINCTCOUNT ( FactTable[CUSTOMER] ),
    FILTER (
        ALL ( 'Date Table' ),
        'Date Table'[Date] <= MAX ( 'Date Table'[Date] )
        && Date Table [Year] = _Year
    )
)