Dax Help- How to calculate the date when Demanded quantity will be fulfilled.

[Anonymous]

Hi , I'm trying to return a date when Demand Quantity will be fulfilled by looking at the Onhand quantity. The data I have is future Projection data for planning. Here's sample dataset;

so for 5/24 demand quantity is 670k and Onhand quanity is 3.5M so the demand can be met on the same week so the return date should be 5/24.  for 5/31 demand is 12M and Onhand is 3.6 so it cannot be fulfilled by 5/31 so I need to check the next date which is 6/7 it has 4.9 M so far cummulative sum for Onhand is 8.5 and it cannot still be fulfilled so have to go to next date 6/14 it has 5.4M so by summing cumulatively 6/14 week will fulfill the demand. I need to calculate all such dates for all the records. 
Here's the sample resultset.

current record should not look back previous records data, it should be all current and next records. 

[Jihwan_Kim]

Hi, @Anonymous 

I am not sure if I understood your question correctly, but please check the below picture and the sample pbix file's link down below.

- I tried to create a sample pbix file by simplifying the sample above.

- All measures are in the sample pbix file, and all steps are numbered in front of each measure.

5 Result Measure =

VAR currentdemandcumulate = [3 demand cumulate]

VAR newtable =

FILTER (

SUMMARIZE (

ALL ( 'Table' ),

'Table'[RetailWeekBeginDate],

"@onhandcumulate", [4 onhand cumulate]

),

[4 onhand cumulate] >= currentdemandcumulate

)

RETURN

IF (

[1 demand total] <= [2 onhand total],

SELECTEDVALUE ( 'Table'[RetailWeekBeginDate] ),

MINX ( newtable, 'Table'[RetailWeekBeginDate] )

)

https://www.dropbox.com/s/807g5mqqyrze4jz/srikanthk.pbix?dl=0 

Hi, My name is Jihwan Kim.

If this post helps, then please consider accept it as the solution to help other members find it faster, and give a big thumbs up.

Linkedin: linkedin.com/in/jihwankim1975/

Twitter: twitter.com/Jihwan_JHKIM

[Anonymous]

so for Retailweekbegin date 6/28/2021 the deamd is 10,641 where as OnHand is only 5420 the demand cannot be fulfilled on that week ( 10641-5420=5221). The remaining demand 5221 can be fulfilled in the next week 7/5/2021 the 5 Result Measure should return 7/5/2021 as date.  

[Anonymous]

@Jihwan_Kim 

[Jihwan_Kim]

Hi, @Anonymous 

Please check the link down below.

5 Result Measure =
VAR currentdemand = [1 demand total]
VAR currentdate =
MAX ( 'Table'[RetailWeekBeginDate] )
VAR newtable =
FILTER (
SUMMARIZE (
FILTER ( ALL ( 'Table' ), 'Table'[RetailWeekBeginDate] >= currentdate ),
'Table'[RetailWeekBeginDate],
'Table'[onhand],
"@onhandcumulate",
SUMX (
FILTER (
ALL ( 'Table' ),
'Table'[RetailWeekBeginDate] >= currentdate
&& 'Table'[RetailWeekBeginDate] <= MAX ( 'Table'[RetailWeekBeginDate] )
),
'Table'[onhand]
)
),
[@onhandcumulate] > currentdemand
)
VAR mindateoverdemand =
MINX ( newtable, 'Table'[RetailWeekBeginDate] )
RETURN
IF (
[1 demand total] <= [2 onhand total],
SELECTEDVALUE ( 'Table'[RetailWeekBeginDate] ),
mindateoverdemand
)

https://www.dropbox.com/s/807g5mqqyrze4jz/srikanthk.pbix?dl=0 

Hi, My name is Jihwan Kim.

If this post helps, then please consider accept it as the solution to help other members find it faster, and give a big thumbs up.

Linkedin: linkedin.com/in/jihwankim1975/

Twitter: twitter.com/Jihwan_JHKIM

[Anonymous]

@Jihwan_Kim That exactly what I'm looking for but it doesn't work on my model. Running into memory issues the table has 800M records.