topic Re: Sum of Units by Invoice Date, Filtered by Ordered Date in DAX Commands and Tips

Sum of Units by Invoice Date, Filtered by Ordered Date

Dave1mo1 — Thu, 01 May 2025 17:30:10 GMT

Hi,

I need to create a measure that sums only units ordered before a specific date. I then need to be able to put it in a table based on the invoiced date. The invoice date is the active relationship between my date table and fact table, and I have an inactive relationship between the date table and the ordered date. I've tried the following DAX but am getting no results (not an error - just a blank table). How can I modify this to get the sum of units only for orders placed before April 30th?

Invoiced Total Units before Increase = calculate(
SUM('Order Fact Table' [Units]),
datesbetween (
'Order Fact Table'[Ordered Date],
BLANK(),
04/30/2025 ))

Re: Sum of Units by Invoice Date, Filtered by Ordered Date

lbendlin — Thu, 01 May 2025 19:40:33 GMT

DATESBETWEEN requires a proper calendar table in your data model and does not work with BLANK() dates.

Invoiced Total Units before Increase = calculate( SUM('Order Fact Table' [Units]), 'Order Fact Table'[Ordered Date] <= dt"2025-04-30")

Re: Sum of Units by Invoice Date, Filtered by Ordered Date

ValtteriN — Thu, 01 May 2025 19:44:38 GMT

Hi,

Here is one way to do this.

data:

Dax:

sum before certain =

var _datemax = DATE(2025,4,30)

var _vt = FILTER(ALLSELECTED('Table (52)'),'Table (52)'[InvoiceDate]<=_datemax)

RETURN

SUMX(_vt,[Value])

This will return 150 since only 2 rows are before the selected datemax.

I hope this post helps to solve your issue and if it does consider accepting it as a solution and giving the post a thumbs up!

My LinkedIn: https://www.linkedin.com/in/n%C3%A4ttiahov-00001/

Re: Sum of Units by Invoice Date, Filtered by Ordered Date

Anonymous — Fri, 02 May 2025 04:59:08 GMT

Hi Dave1mo1,

Thank you for reaching out to the Microsoft Fabric Forum Community.

And aslo Thanks to ValtteriN & lbendlin for Prompt and useful solution.

Try below Dax:

Invoiced Total Units before Increase =
CALCULATE(
SUM('Order Fact Table'[Units]),
'Date Table'[Date] < DATE(2025, 4, 30),
USERELATIONSHIP('Date Table'[Date], 'Order Fact Table'[Ordered Date])
)

Let us know if you need further assistance , we’re happy to help. If this addressed your query, please consider marking the response as an Accepted Solution so it can assist others in the community as well.

Thank you & Regards,
Prasanna kumar

Re: Sum of Units by Invoice Date, Filtered by Ordered Date

Anonymous — Wed, 07 May 2025 06:48:18 GMT

Hi Dave1mo1,

Just following up to see if the solution provided was helpful in resolving your issue. Please feel free to let us know if you need any further assistance.

If the response addressed your query, kindly mark it as Accepted Solution and click Yes if you found it helpful — this will benefit others in the community as well.

Best regards,

Prasanna Kumar

Re: Sum of Units by Invoice Date, Filtered by Ordered Date

Anonymous — Wed, 14 May 2025 09:16:28 GMT

Hi Dave1mo1,

We wanted to kindly check in to see if everything is working as expected after trying the suggested solution. If there’s anything else we can assist with, please don’t hesitate to ask.

If the issue is resolved, we’d appreciate it if you could mark the helpful reply as Accepted Solution — it helps others who might face a similar issue.

Warm regards,

Prasanna Kumar

Re: Sum of Units by Invoice Date, Filtered by Ordered Date

Anonymous — Mon, 19 May 2025 07:00:17 GMT

Hi Dave1mo1 ,

Just a gentle reminder has your issue been resolved? If so, we’d be grateful if you could mark the solution that worked as Accepted Solution, or feel free to share your own if you found a different fix.

This not only closes the loop on your query but also helps others in the community solve similar issues faster.

Thank you for your time and feedback!

Best,

Prasanna Kumar