counting total distinct accounts based off selected period

[learningOh]

Hi everyone,

i like to count the total number of accounts whose lastinvoicedatefirstdate for each of the rows.

for example if i select selected period to be 12 months, then if the current row is 2023 january, i want it to count the total number of acounts found in 2022 january.

context

the idea is that if i am looking at 2023 january. then if an account whose last transaction with us is more than the selected period. which means that they have no activity at all from the last period to now, then we have deemed the account as a lost account.

i have this code but it keeps showing a blank in the table visual.

test3 =

var currentdates = LASTDATE('FACT'[key(calendarfirstdate)])

var lastpdate = dateadd(currentdates,-1*[Selected Period] ,month)

return

CALCULATE(DISTINCTCOUNT('FACT'[account num]),FILTER('FACT',

               'FACT'[lastinvoicedatefirstdate]= lastpdate))


--

i tried this code as well but it also shows blank

test3 = calculate(

DISTINCTCOUNT( 'FACT'[Account Num]), filter( 'FACT', 'FACT'[lastinvoicedatefirstdate] = DATEADD( LASTDATE('FACT'[key(calendarfirstdate)]), -1 * [Selected Period], month)

please help me modify the code or approach to achieve this

[amitchandak]

@learningOh , Try like

test3 =
var currentdates = LASTDATE('FACT'[key(calendarfirstdate)])
var lastpdate = Date(Year(currentdates),month(currentdates)-1*[Selected Period] ,day(currentdates))
return

CALCULATE(DISTINCTCOUNT('FACT'[account num]),FILTER('FACT',
'FACT'[lastinvoicedatefirstdate]= lastpdate))

also refer

Latest
https://amitchandak.medium.com/power-bi-get-the-last-latest-value-of-a-category-d0cf2fcf92d0

https://amitchandak.medium.com/power-bi-get-the-sum-of-the-last-latest-value-of-a-category-f1c839ee8...