Date Measure Calculation

[thomaskelly]

Good Morning

I have a table containing sales order information, each order has a due date for delivery.  I want to have a measure that counts the number of orders where the due date has passed the days current date otr today .  Eg if an order was due yesterday or last week it would be counted if the due date is before today.

I need a measure to calculate today , then to count the orders if then meant the criteria.

Help , or links would be most welcome

Can anyone help?

Many Thanks

[v-huizhn-msft · Accepted solution]

Hi @thomaskelly,

You can use TODAY() function to return the current date. Or you can add a calendar table, and filter the order by comparing the date with the current date in calendar table. I try to reproduce your scenario using the following sample date, which displays the sales order information table.

 

Then create a measure using the formula below. Create a table, select the date in Calendar table, measure as Values level. You will get the expected result as the following screenshot. In the picture, you will get the count of order until the current date. For example, there are 6 orders’ due date passing 2016/11/20 in the first row, and there are 8 orders passing 2016/11/23 and so on.

Measure 2 = CALCULATE(COUNTA(Test3[Order]),FILTER(Test3,Test3[Due date]<MAX('Calendar'[Date])))

Best Regards,
Angelia

[thomaskelly]

Thanks Angelia,

Works great

Kind Regards

Thomas