Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
7 years ago

Stock Cover Calculation

Someone previously provided the following DAX solution to calculate Stock Days cover:

 

Can someone please explain to me how to get this to work?

Column = 
VAR s = Table1[Stock]
VAR w = Table1[Week]
VAR x = Table1[Product]
VAR t =
    FILTER ( Table1, Table1[Week] > w && Table1[Product] = x)
VAR t2 =
    ADDCOLUMNS (
        t,
        "total", SUMX (
            FILTER ( t, Table1[Week] <= EARLIER ( Table1[Week] )&&Table1[Product]=EARLIER(Table1[Product])),
            Table1[Sales Plan]
        )
    )
RETURN
    IF (
        COUNTROWS ( FILTER ( t2, [total] >= s ) )
            > 0,
        COUNTROWS ( FILTER ( t2, [total] < s ) )
            + DIVIDE (
                s
                    - MAXX ( TOPN ( 1, FILTER ( t2, [total] < s ), Table1[Week], DESC ), [total] ),
                MAXX (
                    TOPN ( 1, FILTER ( t2, [total] >= s ), Table1[Week], ASC ),
                    Table1[Sales Plan]
                )
            )
    )

 

4 Replies

    • Anonymous's avatar
      Anonymous
      Not applicable
      DemandSupplyClosingMATNRYear_WeekNoRemain-WK13Remain-WK14Remain-WK15Days_Cover
      059935993ABC2019-135993  37
      703581111101ABC2019-14529011101 60
      608010493ABC2019-154682104931049369
      142009073ABC2019-16326290739073 
      81008263ABC2019-17245282638263 
      21702304029133ABC2019-1828260936093 
      1257027876ABC2019-19-97548364836 
      1240026636ABC2019-20 35963596 
      1720024916ABC2019-21 18761876 
      1120023796ABC2019-22 756756 
      1440022356ABC2019-23 -684-684 
      417021939ABC2019-24    
      226021713ABC2019-25    
      410021303ABC2019-26    
      1052020251ABC2019-27   

       

       

      Formula for Days Cover

      =COUNT(F3:F7)*7+ROUND(F7/A8*7,0)

      • v-lili6-msft's avatar
        v-lili6-msft
        Community Support

        hi, Anonymous 

        If this formula is work on the data above, I think there is no connection between the data and the formula.

        Please check it if you uploaded the wrong data.

         

        Best Regards,

        Lin