Summarized count of past due orders

[adamnimmo]

Hello all, I am trying to create a single card visual which displays a count of my past due orders.

My dataset is setup with a colume of 'Order No' which lists the same value multiple times based on the items within the order, I then have a column 'Order Date' and 'Est Delivery Date' lastly a calculated column 'PastDue' which populates based on 'Order Date < Today()'.

My problem is when creating this card/visual and using the field of 'PastDue' and the count calculation my results are skewed since it is counting each instance, whereas I need it to only count each instace based on a distinct order number.

[v-yuezhe-msft]

@adamnimmo,

Right click your table and select “New Measure”, apply the following formula. Then drag the measure to a Card visual to check if you get expected result.

Measure  = CALCULATE(DISTINCTCOUNT(Table[Order No]),FILTER(Table, Table[Order Date]<TODAY()))

If the above DAX doesn’t help, please share sample data of your table and post desired result here.

Regards,
Lydia

[adamnimmo]

Thank you all for your help, I am able to get this working within a test environment, however it doesn't work with my live data for some reason. I believe it is in the way it is intially formatted so I will continue working with it.

[v-yuezhe-msft]

@adamnimmo,

You can post error message and share sample data of the table for us to analyze.

Regards,
Lydia

[adamnimmo · Accepted solution]

Thanks @v-yuezhe-msftI actually was able to overcome the calcuation issue by creating a helper table which I then created a summarized listing of my orders/dates to allow the calculation to work.

[Ashish_Mathur]

Hi,

Use this calculated field formula

=DISTINCTCOUNT(Data[Order No])

Hope this helps.

[Greg_Deckler]

Sample data would be hugely beneficial.

https://community.powerbi.com/t5/Community-Blog/How-to-Get-Your-Question-Answered-Quickly/ba-p/38490