New customers By Month ( Customer count)

[JimmyKhan2022]

I have a new measure to calculate the number of customers who have revenue this month but have previosuly have 23 months of no revenue. Unfortuntately it appears that DAX is not behaving and it including a few customers it should not be . Can you have a look at the PBIX and see what is missing in the below formula please ?

The isblank condition on the previous 23 months is not working for some reason and its not clear and obvious. 

Power BI File

JS New Cust by Month = 
var result=

CALCULATE( 
Sumx(VALUES('Customer'[customer_id]),if( ISBLANK([JS_Revenue_Prev_23] )    && not(ISBLANK([JS_Revenue])) , 1,BLANK())) , USERELATIONSHIP('Time'[Date],'General Ledger'[date_key]) )

Return result

[JimmyKhan2022]

Thank you @MarkLaf  for responding . 

The logic is that is there is revenue for the customer in the current month and no revenue in the previous 23 financial months then its a NEW customer . 

I have marked the ones that are correct below for Feb 2023.  An incorrect one is like customer id 60129542705 or 34359738641.

[MarkLaf]

I realized that it seems like your scenario is a) select a single Year/Month combo through slicers in tab Graph with Rolling 13 Months, b) display the last 13 months in x axis of line graph from the single Year/Month selection, c) calculate the rolling 13 month summation of new customer, d) enable drillthrough to tab Drillthrough CY where a table with customer details only shows newly added customers for the month.

You are on the right track, but I think your main hangup is you are conflating (b) and (c) such that you are achieving (b) but not (c). You want to use 'Previous Date' for (b), but (c) should follow a more typical moving summation pattern. Also, I would recommend switching the roles of 'Time' and 'Previous Date'; i.e. have 'Previous Date' in the slicer and 'Time' in your visual. Usually, the general approach with tables set up for advanced slicer interactions (e.g. without active relationships) is to use the dummy tables in the slicer.

So, a few suggested tweaks to your visuals and then a new attempt at [Customer_Count_Ignore_Date_filters]:

1) Switch up fields in your visuals so that 'Previous Date' fields are in the slicer and your 'Time' fields are in your visuals.

2) New measure (edit: switched to simpler approach using WINDOW + made display inputs independent from SUMX inputs):

Customer_Count_Ignore_Date_filters_Alt = 
// modified from moving average 3 months at https://www.daxpatterns.com/month-related-calculations/
VAR MonthsInRange = 13
VAR MonthRangeOffset = 0
VAR Period =
    WINDOW(
        1 - MonthsInRange + MonthRangeOffset,
        MonthRangeOffset,
        ALL( 'Time'[YearMonthKey] ),
        ORDERBY( 'Time'[YearMonthKey] )
    )
VAR TopMonthRange =
    TOPN( 1, Period, 'Time'[YearMonthKey], DESC )
VAR Result =
    IF(
        COUNTROWS( Period ) >= MonthsInRange,
        CALCULATE( SUMX( Period, [JS New Cust by Month] ), REMOVEFILTERS( 'Time' ) )
    )
VAR DisplayMonthsInRange = 13
VAR DisplayMonthRangeOffset = 0
VAR DisplayPeriod =
    WINDOW(
        1 - DisplayMonthsInRange + DisplayMonthRangeOffset,
        DisplayMonthRangeOffset,
        ALLSELECTED( 'Previous Date'[YearMonthKey] ),
        ORDERBY( 'Previous Date'[YearMonthKey] )
    )
VAR DisplayCheck = TopMonthRange IN DisplayPeriod
RETURN
    IF( DisplayCheck, Result )

[MarkLaf]

Can you please point out a customer and month for which the count is incorrect? I did my own version of your count measure using WINDOW + putting logic all in one measure and am mostly getting same result.

I say mostly because your measure will still count one for when Customer[customer_id] is blank. I.e. SUM( 'General Ledger'[usd_revenue] ) for 'General Ledger'[customer_id] that do not exist in Customer. If that is your issue, then I think you should be able to fix by replacing VALUES with DISTINCT for your iteration table in SUMX.

Otherwise, it may also be useful to more fully described the desired logic behind the count. E.g., your measure is counting any customer with revenue in the first month of Time (even though, it's probable that this is a continuation of revenue from before the Time cutoff rather than representative of new revenue / new customer.