cancel
Showing results for
Did you mean:

Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.

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:

 Date Warehouse Level Planned Deliveries Predicted Warehouse Level 01.02.2022 100 50 150 02.02.2022 25 175 03.02.2022 75 250

The output I need is:

 Date Prediction 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
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))

2 REPLIES 2
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))

Frequent Visitor

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?

Announcements

#### Microsoft Fabric Learn Together

Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City

#### Power BI Monthly Update - April 2024

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

#### Fabric Community Update - April 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors
Top Kudoed Authors