Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
Hi,
I am working on a DAX solution, where I would need to see the result in the below format.
I would like to see how many weeks it takes for the forecast to run out based on the on-hand value. I have rounded the weeks in the result.
Thanks for the help
Latha
what's a logic for your Result?
I am checking how many weeks it takes the sum of the forecast to reach on-hand value.
Example: In the first row, on-hand is negative so I would need to get 0.
for the second row, 6 weeks of forecast (733+733+857+857+857+3099) is required to get the on-hand 6399.
Please let me know if you further have any question.
it's unclear for me why exactly 6 weeks for second row.
and to be clear, you need DAX for new Result column (highlited as yellow) or for SUM 733+733+857+857+857+3099 ?
I want to do the calculation for the results column highlighted. And to clarify, I am counting the number of forwarding forecast rows needed to sum to equal the ending on-hand value. 6 is a rounded value in the above example, but the actual value would be 5. 6 approx.
I was able to solve this problem. thanks.
Can you please share how you solved it?
It would be really helpful to me
Thanks!
Hi @emma_3
Not sure if this is what you're lookinng for.
Result =
IF (
'Table'[ON-HAND] > 0,
VAR CurrentOnHand = 'Table'[ON-HAND]
VAR CurrentBPTable = FILTER ( 'Table', 'Table'[SKU] = EARLIER ( 'Table'[SKU] ) )
VAR T =
ADDCOLUMNS (
CurrentBPTable,
"@Difference",
VAR CurrentDate = 'Table'[ORDER DATE]
VAR TableOnAndBefore = FILTER ( CurrentBPTable, [ORDER DATE] <= CurrentDate )
VAR CummulativeForecast = SUMX ( TableOnAndBefore, [QUANTITY] )
RETURN
ABS ( CurrentOnHand - CummulativeForecast )
)
VAR MinDiffRecord = TOPN ( 1, T, [@Difference], ASC )
VAR MinDiffDate = MAXX ( MinDiffRecord, [ORDER DATE] )
VAR Result = COUNTROWS ( FILTER ( T, [ORDER DATE] <= MinDiffDate ) )
RETURN
Result,
0
)
User | Count |
---|---|
41 | |
27 | |
23 | |
18 | |
15 |
User | Count |
---|---|
54 | |
35 | |
19 | |
18 | |
15 |