DAX Calculate, Sum, Datesbetween duplicate date error

[DataScope]

I would like to calculate sales for the specific time period (1/1/2021 to 1/31/2021) but keep receiving an error. My data contains a facts table named sales_order_item. Sales_order_item has a row for each item in a sales order, as well as that item's corresponding sale_price. So multiple rows can have the same order_id and same datetime timestamp.

I am using this dax function with a date table:

Promo Sales = CALCULATE(SUM(sales_order_item[sale_pricel]), DATESBETWEEN('Date Table'[Date], DATE(2021,01,01), DATE(2021,01,31)))

When that function is used, the result is blank. See screenshot below:

I also tried to using only the sales order item table, with no other tables in the model. In that case the below DAX function is used, which references order date from the sales_item table instead of the date column from date table:

 Promo Sales = CALCULATE(SUM(sales_order_item[sale_pricel]), DATESBETWEEN('sales_order_item'[order_date], DATE(2021,01,01), DATE(2021,01,31)))

The issue might have to do with the SUM aggregation within a CALCULATE function. But I don't know for sure and cannot figure out how to get the correct function. Any ideas?

[v-xiaotang · Accepted solution]

Hi @DataScope 

if you already have a specific date period, there is no need to use 'Date Table'. In this scenario, you can use the second measure, it calculates total by filter 'sales_order_item' in a period 

Promo Sales 1 = CALCULATE(SUM(sales_order_item[sale_pricel]), filter('sales_order_item', 'sales_order_item'[order_date]>= DATE(2021,01,01) && 'sales_order_item'[order_date]<= DATE(2021,01,31)))

Here's a post for your reference on when to use a calendar table, 

https://community.powerbi.com/t5/Desktop/Calendar-Table/m-p/1281928

Best Regards,

Community Support Team _Tang

If this post helps, please consider Accept it as the solution to help the other members find it more quickly.

[DataScope]

@amitchandak Yes, a value was returned with your DAX code.

[DataScope]

@amitchandak what exactly is going on here? Why does FILTER work when DATESBETWEEN does not?

[amitchandak]

@DataScope , first check does this give any data

Promo Sales = CALCULATE(SUM(sales_order_item[sale_pricel]), filter('sales_order_item', 'sales_order_item'[order_date]>= DATE(2021,01,01) && 'sales_order_item'[order_date]<= DATE(2021,01,31)))

Use this on new blank page and check. To avoid having any filter