topic Re: Trouble calculating the orders and filled within each quarter in DAX Commands and Tips

Trouble calculating the orders and filled within each quarter

Dashboard_Dev — Tue, 10 Oct 2023 00:29:54 GMT

I'm some what new at PBI.

I'm trying to figure out how many orders were placed and also filled within each quarter. I do have a slicer for the Quarter on the report. I'm really stumped as to how to go about getting the calculations. Any help would be appreciated.

Order date	Filled Date
06/08/2023	07/26/2023
06/09/2023	07/24/2023
04/27/2023	07/20/2023
05/24/2023	07/10/2023
06/01/2023	07/03/2023
05/25/2023	06/20/2023
03/08/2023	06/15/2023
05/01/2023	06/15/2023
01/24/2023	06/09/2023
06/08/2023	06/08/2023
04/05/2023	05/31/2023
03/28/2023	05/24/2023
03/14/2023	05/16/2023
03/02/2023	05/08/2023
03/01/2023	05/03/2023
02/06/2023	05/01/2023
02/17/2023	04/28/2023
04/20/2023	04/27/2023
02/25/2023	04/27/2023
03/30/2023	04/18/2023
03/16/2023	03/30/2023
02/21/2023	03/16/2023
01/31/2023	03/10/2023
02/09/2023	03/10/2023
01/12/2023	02/17/2023
02/08/2023	02/08/2023
02/07/2023	02/07/2023
03/02/2023
03/27/2023
04/18/2023
04/20/2023

UPDATE

I actually came up with a solution. I created a column for the quarter for order date and another for filled date. Then I added a conditional column to compare the two columns and return a value, 1 if the orders were placed and filled within the same quarter and 0 if they weren't. Then just added the value of the column in the visual. I can't believe how simple the solution was and how much I was over thinking and complicating this.

Re: Trouble calculating the orders and filled within each quarter

Dangar332 — Sun, 08 Oct 2023 08:16:48 GMT

hi, Dashboard_Dev

(1)

if your slicer data(quarter no) present with same table then use below code

result =

var quarterdata = SELECTEDVALUE('your table'[or qua])

var ordercount = CALCULATE(COUNT('your table'[Filled Date]),QUARTER('your table'[Filled Date])=quarterdata,ALL('your table'[or qua]))

var filledcount = CALCULATE(COUNT('your table'[Order date]),QUARTER('your table'[Order date])=quarterdata,ALL('your table'[Filled Date]))

return ordercount-filledcount

in you data negative value also come as a result measure. you can convert to positive using abs function like below

result =

var quarterdata = SELECTEDVALUE('your table'[or qua])

var ordercount = CALCULATE(COUNT('your table'[Filled Date]),QUARTER('your table'[Filled Date])=quarterdata,ALL('your table'[or qua]))

var filledcount = CALCULATE(COUNT('your table'[Order date]),QUARTER('your table'[Order date])=quarterdata,ALL('your table'[Filled Date]))

return ABS(ordercount-filledcount)

(2)if your slicer data present in another table you try this

result1 =

var quarterdata = SELECTEDVALUE('Table (2)'[quarter])

var ordercount = COUNTX(FILTER('your table',QUARTER('your table'[Order date])=quarterdata),'your table'[Order date])

var filledcount = COUNTX(FILTER('your table',QUARTER('your table'[Filled Date])=quarterdata),'your table'[Filled Date])

return ordercount-filledcount

in you data negative value also come as a result1-measure. you can convert to positive using abs function

Below measure help you to calculate count only those which orderd and filled in same quarter

result2=

var quarterdata = SELECTEDVALUE('your table'[or qua])

var ordercount = CALCULATE(COUNT('your table'[Filled Date]),QUARTER('your table'[Filled Date])=quarterdata)

var filledcount = CALCULATE(COUNT('your table'[Order date]),QUARTER('your table'[Order date])=quarterdata)

return ordercount-filledcount

If this post helps, then please consider Accepting it as the solution to help the other members find it more quickly. Appreciate your kudos.

Re: Trouble calculating the orders and filled within each quarter

Dashboard_Dev — Mon, 09 Oct 2023 23:30:59 GMT

Thank you, but even when I changed the names to the actual table names there were multiple syntax errors.

Re: Trouble calculating the orders and filled within each quarter

Dangar332 — Fri, 13 Oct 2023 12:39:27 GMT

hi, Dashboard_Dev

please refer This .pbix file in which i mention all things which help you.