Using Dates Not in a Date Table to Calculate Open Accounts Receivable

[ScottTPA]

I have a table of invoices with an Invoiced Date and a Paid Date. I also have a date table.

I am trying to show open AR which is equal to $Invoiced Amount where date selected >=Invoiced Date and <=Paid Date. I tried the following:

Open AR $ =

CALCULATE
([Total Open AR],
filter(all(DateTable),
DateTable[Date]>= AR[invoice date] &&
DateTable[Date]<= AR[Paid Date]
)
)

My problem is I can’t get PowerBI to recognize AR[Invoice Date] or AR[Paid Date]. My measure is in the AR table, but it only recognizes dates from the date table. I tried creating a measure for Invoice Date and for Paid Date, but I am not sure what measure would work with dates outside of the Date Table.

[Melissa]

Hi @ScottTPA,

I’m guessing you have an active relationship between your Dates and Invoices table.
That will result in a single row being returned by the Dates table. If there are only inactive relationships both AR[invoice date] and AR[Paid Date] will be considered equal and your measure should work.

Having only inactive relationships means that in other measures you’ll have to “activate” one of the exsisting relationships with the function USERELATIONSHIP inside of CALCULATE.

Some other references can be found here.

Counts number of Incidents with a Date slicer

Hello @supergallagher25, Thank you for posting your query onto the Forum. Just for the reference I’ve changed the relationship in your file which was one-to-one and bi-directional to One-to-Many and single directional as per the fundamentals. Below is the screenshot attached for the reference. [Count no. of incidents - 1] Now, for calculation purpose you can use the formula as shown below since you’ve an inactive relationship. You can turn on this inactive relationship by using the USERELAT…

https://forum.enterprisedna.co/t/how-does-userelationship-compare/4939

https://forum.enterprisedna.co/t/comparing-treatas-vs-userelationship-in-power-bi/11562

I hope this is helpful

[BrianJ]

@ScottTPA,

I think the problem here is not that AR[Invoice Date] is not in the Date table, but the fact that it is a “Naked column reference” , so DAX has no ability to determine what row is being referred to. That term in the filter condition can be a measure or a variable or a column refernce wrapped in an aggregator (e.g., SELECTEDVALUE, MIN or MAX), but not a naked coumn reference.

I hope this is helpful.

• Brian

[Melissa]

Nice one @BrianJ, looking for my spectacles now…

[ScottTPA]

Thank you @Melissa and @BrianJ . I used Selected Value and when I do either >= or <= only, I get a result. See the measure Greater AR As of on the attached.

I don’t understand how I would put a USERELATIONSHIP in a calculate formula where I need the main relationship to work as well.

In other words, my primary relationship is DateTable[Date] to AR[Invoice Date] and I am confused about how to make the USERELATIONSHIP work for DateTable[Date] to AR[Paid Date] in the same measure.

I attached a sample pbix and here is my attempt at this measure that yields an error when I use it in a table.

Open AR As of =
CALCULATE(sum(AR[Amount]),
filter(all(DateTable),
DateTable[Date]<=(SELECTEDVALUE(AR[Invoice Date]) &&
DateTable[Date]>=(SELECTEDVALUE(AR[Paid Date])

))))
Sample Dashboard v09-18-2021 7pm.pbix (271.1 KB)

[Harsh]

Hello @ScottTPA,

Thank You for posting your query onto the Forum.

Well since your condition is like this -

“Date Selected >= Invoice Date and Date Selected <= Paid Date”.

This is where criteria of “Handling Multiple Dates” will come into the picture. Below is the measure and the screenshot of the final results provided for the reference -

Invoice Values Between Dates = 
CALCULATE( SUM( AR[Amount] ) ,
    FILTER( VALUES( AR[Invoice Date] ) ,
        AR[Invoice Date] <= MAX( DateTable[Date] ) ) , 
    FILTER( VALUES( AR[Paid Date] ) , 
        AR[Paid Date] >= MIN( DateTable[Date] ) ) )

I’m also attaching the working of the PBIX file for the reference purposes as well as providing few of the links pertaining to this topic from our blog posts as well as video links from our EDNA YouTube channel.

Hoping you find this useful and meets your requirements that you’ve been looking for.

Thanks and Warm Regards,
Harsh

Sample Dashboard v09-18-2021 7pm - Harsh.pbix (241.9 KB)

How Many Staff Do We Currently Have - Multiple Dates Logic In Power BI Using...

How To Manage Multiple Date Calculations In Your Fact Tables - Advanced Power BI

Multiple Date Functions With VALUES (DAX)

[ScottTPA]

@Harsh , this is really good. Thank you. I like comparing my attempt to your solution and realizing how much I need to learn. Thank you for the references to the video as well. I really appreciate you taking the time to share your knowledge.

[Harsh]

Hello @ScottTPA,

You’re Welcome.

We’re glad that we were able to assist you on this query.

Thanks and Warm Regards,
Harsh