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

The ultimate Microsoft Fabric, Power BI, Azure AI & SQL learning event! Join us in Las Vegas from March 26-28, 2024. Use code MSCUST for a $100 discount. Register Now

Reply
PauSe
Frequent Visitor

DATESBETWEEN / DATESINPERIOD - How to make dynamic for a predictive calcuation

My task is to create a visual to show a predicted Stock level for a warehouse for the next N days. The N is decided by planned delivery dates, it could be three days in the future or thirty.  Let me over-simplify the data to explain my problem:

I have two tables for current stock level: Warehouse.Level;

And planned deliveries: Delivery.DeliveryID, Delivery.Quantity, Delivery.DeliveryDate.

 

I want to write a measure that predicts the changing stock level over the coming days. For each row I need to start with my current Warehouse Level and then add all the delivery quantities from the earliest date to each day in the table. The reference point is always the current Warehouse.Level which we can say is 100, Example:

DateWarehouse Level  Planned Deliveries  Predicted Warehouse Level
01.02.2022   10050150
02.02.2022 25175
03.02.2022 75250

 

The output I need is:

DatePrediction
01.02.2022  150
02.02.2022  175
03.02.2022   250


To get the prediction over the coming days, I decided to use the DATESBETWEEN function but this is where I fail!

MyMeasure = CALCULATE(  SUM(Warehouse[Level]) + SUM(Delivery[Quantity]),

DATESBETWEEN(Delivery[DeliveryDate], TODAY(), The_date_of_the_row  )   )

 

Now the problem is that third expression in DATESBETWEEN because it needs to be the row date, which is dynamic and I cannot figure out how to get it in the statement! Same issue with DATESINPERIOD.  Any idea? 

1 ACCEPTED SOLUTION
amitchandak
Super User
Super User

@PauSe , datesbetween and dateinperiod will require date table

 

MyMeasure = SUM(Warehouse[Level]) + CALCULATE( SUM(Delivery[Quantity]),filter(allselected(Delivery), Delivery[Date]<= Max(Delivery[Date])))

 

Any time you want to date of the row use min or max

 

example

LTD =
var _max = if(isfiltered('Date'),MAX( 'Date'[Date]) , today())
var _min = Minx(ALLSELECTED('Date'),'Date'[Date])
return
CALCULATE([net] ,DATESBETWEEN('Date'[Date],_min,_max))

 

Rolling 12 = CALCULATE(sum(Sales[Sales Amount]),DATESINPERIOD('Date'[Date ],MAX(Sales[Sales Date]),-12,MONTH))

View solution in original post

2 REPLIES 2
amitchandak
Super User
Super User

@PauSe , datesbetween and dateinperiod will require date table

 

MyMeasure = SUM(Warehouse[Level]) + CALCULATE( SUM(Delivery[Quantity]),filter(allselected(Delivery), Delivery[Date]<= Max(Delivery[Date])))

 

Any time you want to date of the row use min or max

 

example

LTD =
var _max = if(isfiltered('Date'),MAX( 'Date'[Date]) , today())
var _min = Minx(ALLSELECTED('Date'),'Date'[Date])
return
CALCULATE([net] ,DATESBETWEEN('Date'[Date],_min,_max))

 

Rolling 12 = CALCULATE(sum(Sales[Sales Amount]),DATESINPERIOD('Date'[Date ],MAX(Sales[Sales Date]),-12,MONTH))

Thanks Amit, I understand that these functions require a table and that is specified in the first clause. However reading your reply I am to understand that using a MAX(whateverDate) will return the row in focus, kind of liek a pointer going through the list?

Helpful resources

Announcements
Fabric Community Conference

Microsoft Fabric Community Conference

Join us at our first-ever Microsoft Fabric Community Conference, March 26-28, 2024 in Las Vegas with 100+ sessions by community experts and Microsoft engineering.

February 2024 Update Carousel

Power BI Monthly Update - February 2024

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

Fabric Career Hub

Microsoft Fabric Career Hub

Explore career paths and learn resources in Fabric.

Fabric Partner Community

Microsoft Fabric Partner Community

Engage with the Fabric engineering team, hear of product updates, business opportunities, and resources in the Fabric Partner Community.

Top Solution Authors