cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
rashesh
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 

-30.png
                 

 

 

 

 

 

 

 

MY OP:-
eOP.png

 

 

 



 

 

 

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

Thank you!! @amitchandak @Ashish_Mathur @ValtteriN 

 

3 REPLIES 3
Ashish_Mathur
Super User
Super User

Hi,

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


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/
wdx223_Daniel
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.


Re.png


 

 

 

Helpful resources

Announcements
May 2023 update

Power BI May 2023 Update

Find out more about the May 2023 update.

Submit your Data Story

Data Stories Gallery

Share your Data Story with the Community in the Data Stories Gallery.

Top Solution Authors
Top Kudoed Authors