Forum Discussion
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 co...
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,
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,
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