Forum Discussion

Vincent_TS794's avatar
Vincent_TS794
Regular Visitor
7 years ago
Solved

Stock Coverage Calculation

Hello All, 

I'm workiong on stock coverage calucation in PowerBi.

Stock coverage is calculated in excel in below way,

IF (Total Stock cover more than 1 month forecast, 30, days that are able to cover) , then , all postive numbers will be sumed up. 

 

 

What i'm trying to do is to directly prepare a colum in PowerBi to calculate the data.

May I know if its possible to do so?

Thanks so much

 

Bests, 

Vincent

Need Help
Tutorial Requests
  • v-cherch-msft's avatar
    v-cherch-msft
    7 years ago

    Hi Vincent_TS794 

    You may use VAR function to hide the columns.Please check below formula:

    Column =
VAR _m1 = [TTL Stock]
VAR _m2 = [TTL Stock] - ( [m1] + [m2] )
VAR _m3 = [TTL Stock] - ( [m1] + [m2] )
VAR _m4 = [TTL Stock] - ( [m1] + [m2] + [m3] )
VAR _m5 = [TTL Stock] - ( [m1] + [m2] + [m3] + [m4] )
VAR _m6 = [TTL Stock] - ( [m1] + [m2] + [m3] + [m4] + [m5] )
VAR m1 =
    IF ( _m1 >= 0, 30, _m1 / [m1] * 30 )
VAR m2 =
    IF ( _m2 >= 0, 30, _m2 / [m2] * 30 )
VAR m3 =
    IF ( _m3 >= 0, 30, _m3 / [m3] * 30 )
VAR m4 =
    IF ( _m4 >= 0, 30, _m4 / [m4] * 30 )
VAR m5 =
    IF ( _m5 >= 0, 30, _m5 / [m5] * 30 )
VAR m6 =
    IF ( _m6 >= 0, 30, _m6 / [m6] * 30 )
RETURN
    IF ( m1 > 0, m1 )
        + IF ( m2 > 0, m2 )
        + IF ( m3 > 0, m3 )
        + IF ( m4 > 0, m4 )
        + IF ( m5 > 0, m5 )
        + IF ( m6 > 0, m6 )

    Regards,

  • v-cherch-msft's avatar
    v-cherch-msft
    7 years ago

    Hi Vincent_TS794 

    Show you the m3 m4 m5 measures as example.

    m3_ = SUM([TTL Stock])-(SUM([m1])+SUM([m2])+SUM([m3]))
    m4_ = SUM([TTL Stock])-(SUM([m1])+SUM([m2])+SUM([m3])+SUM([m4]))  
    m5_ = SUM([TTL Stock])-(SUM([m1])+SUM([m2])+SUM([m3])+SUM([m4])+SUM([m5]))
    Coverage_m4 = IF([m4_]>0,30,[m3_]/SUM([m4])*30)
    Coverage_m5 = IF([m5_]>0,30,[m4_]/SUM([m5])*30)
    Coverage Calculation = IF([Coverage_m4]>0,[Coverage_m4])+IF([Coverage_m5]>0,[Coverage_m5])

    Regards,

6 Replies

  • v-cherch-msft's avatar
    v-cherch-msft
    Microsoft Employee
    7 years ago

    Hi Vincent_TS794 

    Show you the m3 m4 m5 measures as example.

    m3_ = SUM([TTL Stock])-(SUM([m1])+SUM([m2])+SUM([m3]))
    m4_ = SUM([TTL Stock])-(SUM([m1])+SUM([m2])+SUM([m3])+SUM([m4]))  
    m5_ = SUM([TTL Stock])-(SUM([m1])+SUM([m2])+SUM([m3])+SUM([m4])+SUM([m5]))
    Coverage_m4 = IF([m4_]>0,30,[m3_]/SUM([m4])*30)
    Coverage_m5 = IF([m5_]>0,30,[m4_]/SUM([m5])*30)
    Coverage Calculation = IF([Coverage_m4]>0,[Coverage_m4])+IF([Coverage_m5]>0,[Coverage_m5])

    Regards,

    • Vincent_TS794's avatar
      Vincent_TS794
      Regular Visitor
      7 years ago

      Hi v-cherch-msft ,

       

      May you suggest what DAX formula can be used based on the excel formula above?

      I tired to use the quick measure and keep having errors.

      Thanks

       

      Best Regards,

      Vincent

      • v-cherch-msft's avatar
        v-cherch-msft
        Microsoft Employee
        7 years ago

        Hi Vincent_TS794 

        You may use VAR function to hide the columns.Please check below formula:

        Column =
VAR _m1 = [TTL Stock]
VAR _m2 = [TTL Stock] - ( [m1] + [m2] )
VAR _m3 = [TTL Stock] - ( [m1] + [m2] )
VAR _m4 = [TTL Stock] - ( [m1] + [m2] + [m3] )
VAR _m5 = [TTL Stock] - ( [m1] + [m2] + [m3] + [m4] )
VAR _m6 = [TTL Stock] - ( [m1] + [m2] + [m3] + [m4] + [m5] )
VAR m1 =
    IF ( _m1 >= 0, 30, _m1 / [m1] * 30 )
VAR m2 =
    IF ( _m2 >= 0, 30, _m2 / [m2] * 30 )
VAR m3 =
    IF ( _m3 >= 0, 30, _m3 / [m3] * 30 )
VAR m4 =
    IF ( _m4 >= 0, 30, _m4 / [m4] * 30 )
VAR m5 =
    IF ( _m5 >= 0, 30, _m5 / [m5] * 30 )
VAR m6 =
    IF ( _m6 >= 0, 30, _m6 / [m6] * 30 )
RETURN
    IF ( m1 > 0, m1 )
        + IF ( m2 > 0, m2 )
        + IF ( m3 > 0, m3 )
        + IF ( m4 > 0, m4 )
        + IF ( m5 > 0, m5 )
        + IF ( m6 > 0, m6 )

        Regards,

  • Sakolina's avatar
    Sakolina
    New Member
    2 years ago

    Hello Community,

     

    I am working on a very simple problem in excel that seems to be super complicated when it comes to calculation in Power BI. Hopefully someone can help me.

     

    Below is my example. All data are in columns, country, year, month, SKU of product, sell out and stock. I need to calculate stock coverage.

     

     

     

    In excel formula is very simple, and it is = stock for current month / average sell out for previous 3 months (curent month - 3)

     

    In PBI I simply cannot get results for previous 3 months not including current one.

     

    Can anyone help me with this?

     

    Thank oyu in advance,

     

    Cheers,

    Sanja