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

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
lathamadhavi96
Regular Visitor

DAX: Need help with Weeks of supply calculation

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. 

lathamadhavi96_0-1594634712419.png

 

Thanks for the help
Latha

 

7 REPLIES 7
az38
Community Champion
Community Champion

@lathamadhavi96 

what's a logic for your Result?


do not hesitate to give a kudo to useful posts and mark solutions as solution
LinkedIn

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. 

@lathamadhavi96 

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 ?


do not hesitate to give a kudo to useful posts and mark solutions as solution
LinkedIn

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. 

Hi @lathamadhavi96 

Can you please share how you solved it? 

It would be really helpful to me

Thanks! 

Sample pbix 

Hi @emma_3 
Not sure if this is what you're lookinng for.

1.png

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
)

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

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

Top Solution Authors