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

BIstudent_0-1638025035859.png

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. 

 

Thanks in advance.

 

 

 

 

3 REPLIES 3
speedramps
Super User
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]
aj1973
Community Champion
Community Champion

HI @BIstudent 

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

aj1973_0-1638028625932.png

 

Regards
Amine Jerbi

If I answered your question, please mark this thread as accepted
and you can follow me on
My Website, LinkedIn and Facebook

negi007
Community Champion
Community Champion

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




Did I answer your question? Mark my post as a solution!
Appreciate your Kudos



Proud to be a Super User!


Follow me on linkedin

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.