How to convert measure to 13 Rolling Months ?

[JimmyKhan2022]

( NEW ATTEMPT - NOT WORKING ) 


_A_CURRENT_TOTAL_CUSTOMER_COUNT = var flag = 

CALCULATE ( DISTINCTCOUNT('Finance Table'[customer_id]) 
)   

return IF ( [_A_CURRENT_MONTH_NEW CUST FLAG]=1, flag, BLANK() ) 

_A_Current_Month_Customers = 


// For Jan 2023 , this should be 28/01/2023 - Correct
VAR ReferenceDate = MAX('Time'[Date] ) 

// Work out Monthkey for SELECTED MONTH 

Var Monthkey = CALCULATE (
        MAXX ( FILTER ( 'Time', [Date] = ReferenceDate), [YearMonthKey] ),
        REMOVEFILTERS ( 'Time' )	)

// For Jan 2023 , this should be 28/01/2023 - Correct
VAR _FiscalWeekEnd_YearEnd =
    CALCULATE (
        MAXX ( FILTER ( 'Time', [YearMonthKey] =Monthkey), [Date] ),
        REMOVEFILTERS ( 'Time' )	) 

// It is 28/01/2022
VAR _OneYearAgo = Monthkey-12

// 23/01/2022
VAR _FiscalWeekEnd_YearStart =
    CALCULATE (
        MINX ( FILTER ( 'Time', [YearMonthKey] =_OneYearAgo), [Date] ),
        REMOVEFILTERS ( 'Time' )	) 

VAR PreviousDates= 

DATESBETWEEN('Previous Date'[Date],_FiscalWeekEnd_YearStart, _FiscalWeekEnd_YearEnd ) 


var result = CALCULATE(   
  [_A_CURRENT_TOTAL_CUSTOMER_COUNT]
,REMOVEFILTERS('Time'),
KEEPFILTERS(PreviousDates),
USERELATIONSHIP('Time'[Date],'Previous Date'[Date]) )  

RETURN 
 IF ( result = 0, BLANK (),    IF ( [_A_CURRENT_MONTH_NEW CUST FLAG]=1, result, BLANK() )    

I have a page where a user selects a Financial Month and on that page I have a table of customers with this filter below that when it equals 1 , then it in the table shows the NEW Customers that month . 

Now I need to create a 13 month rolling graph and I need to use this measure to give me a number of customers every month. I have tried to use this measure in a 13 month ( graph ) but it does not work ? Any ideas ?  

( NEW ATTEMPT - NOT WORKING ) 

_A_CURRENT_TOTAL_CUSTOMER_COUNT = var flag = 

CALCULATE ( DISTINCTCOUNT('Finacne Table'[customer_id]) 
)   

return flag


( WORKING MEASURE ! ) 

_A_CURRENT_MONTH_NEW CUST FLAG = 

// SELECTED FINANCIAL MONTH
var _selected_Fin_Year_Month = MAX('Time'[YearMonthKey])

// DATE OF LATEST REVENUE RECORD WITH PROVISION FOR SLEECTED MONTH ON PAGE 
var _date = CALCULATE(MAX('Finance Table'[date_key]), 
                            ALL('Time'),
                        'Finance Table'[usd_revenue] > 0, 'Time'[YearMonthKey] <=_selected_Fin_Year_Month
                        ) 

var _month = LOOKUPVALUE('Time'[YearMonthKey], 'Time'[Date], _date)

var _prev23Revenue = CALCULATE(SUM('Finance Table'[usd_revenue]),
                        ALL('Time'),
                        'Time'[YearMonthKey] >= _month-23 && 'Time'[YearMonthKey] < _month)

var _24_months_Available_To_Subtract = CALCULATE(DISTINCTCOUNT('Time'[YearMonthKey] ),
                        ALL('Time'),
                        'Time'[YearMonthKey] >= _month-23 && 'Time'[YearMonthKey] < _month && 'Time'[YearMonthKey] > 24241) // Need to make this part dynamic 

return 
IF (
[_A_Revenue USD] > 0 && (_prev23Revenue = 0 || _prev23Revenue = BLANK()) && _24_months_Available_To_Subtract=23, 1, 0 )

 
Attempt 1 

[amitchandak]

@JimmyKhan2022 , If you have measure M1 and Date table, then rolling 13

Rolling 13 = CALCULATE(sum(Sales[Sales Amount]),DATESINPERIOD('Date'[Date],MAX('Date'[Date]),-13,MONTH))

New Window function

Rolling 13 = CALCULATE([M1], Window(-12,REL,0,REL, ALLSELECTED('Date'[Date]),ORDERBY([Date],ASC)))

Rolling Months Formula: https://youtu.be/GS5O4G81fww

Power BI Window function Rolling, Cumulative/Running Total, WTD, MTD, QTD, YTD, FYTD: https://youtu.be/nxc_IWl-tTc