Showing results for 
Search instead for 
Did you mean: 
Frequent Visitor

CALCULATE function with DATEBETWEEN function showing NULL values

Hi All,
I have three tables ("Date Table", "Inventory", and " sales" ). I want the last 30 days' sales of quantity for each item from the "current date"(This column in the "Inventory" table). I have tried but it showing blank.

 Sales TableSales Table

Date TableDate TableInventory TableInventory Table                                                                   
   I have tried the below measures 
















And please tell me why this is happening and what's the solution. 

Thank you!! @amitchandak @Ashish_Mathur @ValtteriN 


Super User
Super User


Your question is not clear.  Share data in a format the can be pasted in an MS Excel file and show the expected result.

Ashish Mathur
Super User
Super User

1, you need to mark Calendar Table as Date Table

2, Create one-to-many relationship from Calendar to Inventory, and to Sales

3, try this measure

 Last30Days=VAR _date=Max(Inventory[Current Date]) VAR _item=MAX(Inventory[Item_ID]) RETURN CALCULATE(SUM(Sales[Quantity]),DATESINPERIOD(Calendar[Date],_date,-30,DAY),Sales[ItemID]=_item)

Hi Daniel,
Thanks for Ans but it is not working. and still getting the same error as before.
And for your second point I can not able to make the relationship between the calendar and inventory and when I try to it is creating inactive.





Helpful resources

Join Arun Ulag at MPPC23

Join Arun Ulag at MPPC23

Get a sneak peek into this year's Power Platform Conference Keynote.

PBI Sept Update Carousel

Power BI September 2023 Update

Take a look at the September 2023 Power BI update to learn more.

Learn Live

Learn Live: Event Series

Join Microsoft Reactor and learn from developers.

Dashboard in a day with date

Exclusive opportunity for Women!

Join us for a free, hands-on Microsoft workshop led by women trainers for women where you will learn how to build a Dashboard in a Day!

Top Solution Authors
Top Kudoed Authors