cancel
Showing results for
Did you mean:  Helper IV

## Counting Number of records based on calculation using date slicer

Hi

I am wondering if someone could assist me with some dax as I am struggling to get the required output I need here .

This may have been asked before but I have searched the posts on here and haven't quite found what I am looking for .

I have a query "Query 1" which is an order payments table which tracks dates people are due to make a payment, expected amount and received amount per order number . I have added in a difference column which is simply expected - received . I also have a date table which is has a relationship to the payment due date .

What I am trying to do is get a number of orders that have an difference amount for payments up until the end date of the slicer shown above.

If we take the above example , I have the first visual which shows this on a the difference on a payment level , and the second visual shows the total difference within that range per order number.

What I need to do is get a total number of orders where the overall difference up until the end date of the slicer is greater than 0.

Effectively this

count of order number where the sum of difference is > 0 for payments <= 01/04/2021 (slicer end date)

I have tried the following attempt at the dax but I cant seem to get this to work as I need. Anyone able to assist ?

1 ACCEPTED SOLUTION  Community Support

Hi @Arranafc19 ,

Try to create a measure like this:

``````Count =
VAR tab =
SUMMARIZE (
FILTER (
ALL ( 'Table' ),
'Table'[Difference] > 0
&& 'Table'[Payment Due Date]
<= CALCULATE ( MAX ( 'Date'[Date] ), ALLSELECTED ( 'Date' ) )
),
'Table'[Order Number],
"Sum_Difference", SUM ( 'Table'[Difference] )
)
RETURN
COUNTX ( tab, [Order Number] )
`````` Attached a sample file in the below, hopes it could help.

Best Regards,
Community Support Team _ Yingjie Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

6 REPLIES 6  Community Support

Hi @Arranafc19 ,

Try to create a measure like this:

``````Count =
VAR tab =
SUMMARIZE (
FILTER (
ALL ( 'Table' ),
'Table'[Difference] > 0
&& 'Table'[Payment Due Date]
<= CALCULATE ( MAX ( 'Date'[Date] ), ALLSELECTED ( 'Date' ) )
),
'Table'[Order Number],
"Sum_Difference", SUM ( 'Table'[Difference] )
)
RETURN
COUNTX ( tab, [Order Number] )
`````` Attached a sample file in the below, hopes it could help.

Best Regards,
Community Support Team _ Yingjie Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.  Super User IV

@Arranafc19 , Try  like

measure =
var _max = maxx(allselected('Date'), 'Date'[Date])
return
calculate(sum(Query1[difference1]), filter('Date', 'Date'[Date] <=_max))

or

measure =
var _max = maxx(allselected('Date'), 'Date'[Date])
var _sum =calculate(sum(Query1[difference1]), filter('Date', 'Date'[Date] <=_max))
return
sumx(filter(values(Order_number]), _sum>0), _sum)

Proud to be a Super User!  Helper IV

this is effectively what I am trying to matchup in sql form I need to get the balance of expected - received for payments due up until the end date of the slicer. I have managed to get this on an order level with using this measure for the balance and then this measure for a check if the balance is greater than 0 however when I then try and get a count of orders where the balance is > 0 before the end date I cant get the same figure, only works when I include the order number in the visual.

Using this dax to count I get the following whereas when I filter on an order level and count I get the correct value   Super User IV

@Arranafc19 , I think you need count order number

Try like

measure =
var _max = maxx(allselected('Date'), 'Date'[Date])
var _sum =calculate(sum(Query1[difference1]), filter('Date', 'Date'[Date] <=_max))

//or on payment due date

//var _sum =calculate(sum(Query1[difference1]), filter('Query1', 'Query1'[payment sue Date] <=_max))
return
Countx(filter(values(Order_number]), _sum>0), [Order_number])

Proud to be a Super User!  Helper IV

this is not working , it is now pulling all orders. I think the issue here is that the check on the sum is doing this on a payment date level , so when I check for those >0 , it is pulling any order that had a difference greater than 0 on any of the payment lines , where I need this to total expected - total received up to the date and if that has a value > 0 then it needs to be counted  Helper IV

Hi @amitchandak ,

What I am actually looking for is a count of order numbers that have a difference.

So effectively , count(order number) > sum(difference) up until end date of slicer > 0 Announcements #### Welcome to the User Group Public Preview  