Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
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 ?
Solved! Go to Solution.
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.
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.
@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)
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
@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])
hi @amitchandak
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
Hi @amitchandak ,
Thanks for your response,
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
User | Count |
---|---|
139 | |
113 | |
103 | |
73 | |
63 |
User | Count |
---|---|
136 | |
125 | |
107 | |
70 | |
61 |