Formula to add 30 days to a date

[Anonymous]

Hi all,

I am using a CALCULATE formula to try to return a count of the number of documents which have been paid in 30 days or less. I can quite easily and correctly return the number of paid documents using

PaidDocuments = 
CALCULATE(
    COUNTA(IMSDocuments[Unique Document Id]) + 0,
    USERELATIONSHIP(Dates[Date], IMSDocuments[Payment Performance Adjusted Payment Date])
)

But when I add in the extra filter to try and return the documents which have been paid in 30 days or less since being received (payment day minus creation date), it only seems to return documents which have been created and paid on the same day. This is the DAX I am using

PaymentsMadeWithin30DaysCount = 
CALCULATE(
    COUNTA(IMSDocuments[Unique Document Id]) + 0,
    FILTER(IMSDocuments, IMSDocuments[Payment Performance Adjusted Payment Date] - IMSDocuments[Creation Date] <= 30), -- this is the problem line
    USERELATIONSHIP(Dates[Date], IMSDocuments[Payment Performance Adjusted Payment Date])
)

I think the problem could be in my relationships so have attached a stripped back copy of my report here PaymentPerformance.pbix. Can somebody correct where I have gone wrong?

Thanks in advance!

[Anonymous · Accepted solution]

I have solved my own problem using a CROSSFILTER.

PaymentsMadeWithin30DaysCount = 
CALCULATE(
    CALCULATE(
        COUNTA(IMSDocuments[Unique Document Id]) + 0,
        FILTER(IMSDocuments, IMSDocuments[Payment Performance Adjusted Payment Date] <= IMSDocuments[Creation Date] + 30),
        USERELATIONSHIP(Dates[Date], IMSDocuments[Payment Performance Adjusted Payment Date])
    ),
    CROSSFILTER(Dates[Date], IMSDocuments[Creation Date], NONE)
)

It turns out that the DAX was filtering based on an exisiting (important) active relationship. Crossfilter ignores that relationship and only uses the one I want it to use.