Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
4 years ago
Solved

Counting Net New and Net Loss

Every week I receive a report on the current expired payments.  If the payment showed up in 2/2/2022 and 2/9/2022 you know it remained expired, however if it doesnt show up in the 2/16/22 file you kn...
Formula
  • Anonymous's avatar
    Anonymous
    4 years ago

    Hi Anonymous ,

     

    Check the formulas.

    this_week = 
var _thisweek = WEEKNUM(TODAY())
var thisweek_ids = CALCULATETABLE(VALUES('Table'[Unique Payment ID]),FILTER(ALLSELECTED('Table'),WEEKNUM('Table'[File Date])=_thisweek))
return
CALCULATE(DISTINCTCOUNT('Table'[Unique Payment ID]),FILTER(ALLSELECTED('Table'),NOT('Table'[Unique Payment ID] in thisweek_ids)))

last_week = 
var _thisweek = WEEKNUM(TODAY())
var thisweek_ids = CALCULATETABLE(VALUES('Table'[Unique Payment ID]),FILTER(ALLSELECTED('Table'),WEEKNUM('Table'[File Date])=_thisweek-1))
return
CALCULATE(DISTINCTCOUNT('Table'[Unique Payment ID]),FILTER(ALLSELECTED('Table'),NOT('Table'[Unique Payment ID] in thisweek_ids)))

     

     

    Best Regards,

    Jay

Russell-PBI's avatar
Russell-PBI
Icon for Resolver II rankResolver II
4 years ago

Hi Anonymous, does this work?:

Unprocessed = 
VAR MaxDate = CALCULATE(MAX('Table'[File Date]), ALL('Table'))
VAR Unprocessed = CALCULATE(
DISTINCTCOUNT('Table'[Unique Payment ID])
, 'Table'[File Date] = MaxDate
)
RETURN
IF(ISBLANK(Unprocessed), 0, Unprocessed)

Processed =
DISTINCTCOUNT('Table'[Unique Payment ID]) - [Unprocessed]