Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Arranafc19
Helper IV
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 .

 

Arranafc19_1-1617269848201.png

 

 

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.

 

Arranafc19_2-1617270019718.png

 

Anyone able to assist ?

 

1 ACCEPTED SOLUTION
v-yingjl
Community Support
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] )

count .png

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.

View solution in original post

6 REPLIES 6
v-yingjl
Community Support
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] )

count .png

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.

amitchandak
Super User
Super User

@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)

@amitchandak 

this is effectively what I am trying to matchup in sql form

Arranafc19_0-1617286821457.png

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 

 

Arranafc19_1-1617287066098.png

 

and then this measure for a check if the balance is greater than 0 

 

Arranafc19_2-1617287091230.png

 

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 

Arranafc19_0-1617287369800.png

 

whereas when I filter on an order level and count I get the correct value 

 

Arranafc19_1-1617287440351.png

 

@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.

Arranafc19_0-1617288801380.png

 

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

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.