Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

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
Europe Fabric Conference

Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

Power BI Carousel June 2024

Power BI Monthly Update - June 2024

Check out the June 2024 Power BI update to learn about new features.

RTI Forums Carousel3

New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.