Calculate Active Customers

[Mikebuci99]

Hello, Thanks in advance for your assistance. 

The following DAX lists the number of Active customers during a given month. My Years range over 2021, 2022 and will go into 2023. I don't want to show numbers for future dates. As we move into 2023, I don't want it to show monthly numbers for future Months. When I filter out the Month, it doesn't show any data for Dec of 2021.

How do I show the EndDatePerVisual to show MAX('Calendar'[Date]) as the last day of the month for all prior months but show Today() for the current Month & Year. 

Number of Active Customers =

var EndDatePerVisual = MAX('Calendar'[Date])

var ActiveCustomers =

CALCULATE(

    [Measure Count of New Customers],

    REMOVEFILTERS('Calendar'),

    'CustomerAccounts[Created Date] <=EndDatePerVisual,

    'CustomerAccounts'[Account Closure Date] > EndDatePerVisual

    || ISBLANK('CustomerAccounts'[Account Closure Date])

)

VAR Result =

If(

    Month(EndDatePerVisual) <= Month(TODAY()),

    ActiveCustomers

)

Return

Result

[daXtreme]

Here is your problem:

If(
    Month(EndDatePerVisual) <= Month(TODAY()),
    ActiveCustomers
)

The comparison makes sure that year is not taken into account, only the month. You should check not only the month but the year as well. Even better, you should have a column in your date table that marks days as "past", "today", "future." Then it's easy to filter for past+today in any of your measures or via slicers.

[Mikebuci99]

Thanks for the response. I will look work on adding the recommended column to the date table. 

How would I add the Year and Month to this section though?

If( Month(EndDatePerVisual) <= Month(TODAY()), ActiveCustomers )

[amitchandak]

@Mikebuci99 , refer this blog and file dealing with two dates to find active

https://community.powerbi.com/t5/Community-Blog/HR-Analytics-Active-Employee-Hire-and-Termination-tr...