cancel
Showing results for
Did you mean:
New Member

## Calculating The Number of Orders Per Day

Hi,

I am using the Adventure Works 2020 Data Model. I have included a simplified example of the tables.

I would like to create a measure that would allow me to display the number of orders per day or other period specified in a chart.  I have been able to create a table using the following DAX code:

TestTable = SUMMARIZE(Sales, 'Date'[Date], "Number of Orders" ,DISTINCTCOUNT('Sales Order'[Sales Order]))

but I am having trouble getting to returing a single value required for a measure in a chart.

3 REPLIES 3
Super User

The best solution is reshape the input data to 1 calendar dimension table and 1 fact sales table using PowerQuery.
Then the DAX is easy. Howvere ,if you want or need to do it the hard way ....

Create 2 new measures

Sales indicator =
// This will return 1 if the selected period (year/month) has any sales records
// we use ISEMPTY with a NOT because it is more efficient than counting lots of records
// then use INT to transform true/false to an integer because boolean variables cant be used in the Filters pane
INT(NOT(ISEMPTY(Sales)))

Orders with sales =
// get a subset of records that are in your context (eg year/month)
// then count the orders in the subset
VAR
mysubset = FILTER('Sales Order',[Sales indicator] = 1)
RETURN
CALCULATE(
DISTINCTCOUNT('Sales Order'[Sales Order]),
mysubset )

Create and check a report with ...
year, month and [Orders with sales]
Community Champion

It looks like you have made a mistake in the name of the Table "Dates"

Regards
Amine Jerbi

and you can follow me on

Super User

@BIstudent  in this case, you could have created a simple measure like below and then it will accordingly will show the data as per time period selected (year, month, week, day etc.) in your visual

Orders_daily = distinctcount(sales_order)

let me know if i am missing something here.

Proud to be a Super User!

Announcements

#### The Power BI Community Show

Welcome to the Power BI Community Show! Jeroen ter Heerdt talks about the importance of Data Modeling.

#### Charticulator Design Challenge

Put your data visualization and design skills to the test! This exciting challenge is happening now through May 31st!

#### Check it out!

Mark your calendars and join us on Thursday, May 26 at 11a PDT for a great session with Ted Pattison!