Stock Coverage Calculation

[Vincent_TS794]

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

[v-cherch-msft · Accepted solution]

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]

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 

[v-cherch-msft · Accepted solution]

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,

[v-cherch-msft]

Hi @Vincent_TS794 

It seems you may also need to create several columns to get the result in power bi.

Regards,

[Vincent_TS794]

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 · Accepted solution]

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,

[Vincent_TS794]

Thanks Cherie @v-cherch-msft  for your reply , much appreciated 🙂

However, we found out the column doesnt work this case, as it will sum up the coverage of all skus.

Is it possible to do by calcualte by "Measure" instead?

Thanks.

Best Regards,

Vincent