Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
Hello everyone,
I'm triying to build a meaure to calculate days of stock
This are calculated this way: Days Of Stock= (Sales of last month/Stock of next month)*30
For example, For calculating days of Stock Of Feburary=(Sales of January/Stock Of February)*30
I have two tables, 'Consolidado2018' for sales Sales and 'StockConsolidiado2' for Stock. Each has the code of the product, name, and the date(sale or stock)
I tried with this measure but I doesn't work the way I want.
Dias de Inventario = iferror(calculate(sum(Consolidado2018[UNIDADES]),FILTER(Consolidado2018,MONTH(Consolidado2018[Fecha]=month(today()))))/calculate(sum(StockConsolidado2[PZAS]),FILTER(StockConsolidado2,month(StockConsolidado2[FECHA]=month(today()+1)))),0)*30Hope you can help me.
Solved! Go to Solution.
Hi @Anonymous ,
As your description, I use the measure to achieve to get the result Days of stock=(current month sales/previous month stock)*30. As in your table, there is no data for stock for Feb 2019.
Days of stock = VAR ym = YEAR ( TODAY () ) * 100 + MONTH ( TODAY () ) VAR pm = YEAR ( EDATE ( TODAY (), -1 ) ) * 100 + MONTH ( EDATE ( TODAY (), -1 ) ) VAR curentamount = CALCULATE ( SUM ( Sales[UNIDADES] ), FILTER ( Sales, Sales[Año] * 100 + Sales[Mes] = ym ) ) VAR previousamount = CALCULATE ( SUM ( Stock[PZAS] ), FILTER ( Stock, 'Stock'[AÑO] * 100 + Stock[MES] = pm ) ) RETURN curentamount / previousamount * 30
Also please find the pbix as attached.
Regards,
Frank
See if my Time Intelligence the Hard Way provides a different way of accomplishing what you are going for.
https://community.powerbi.com/t5/Quick-Measures-Gallery/Time-Intelligence-quot-The-Hard-Way-quot-TIT...
Very good information but still I can't solve my issue.
@Anonymous
As Greg said there are many ways to achieve it. Can you try this also.
Days Of Stock = CALCULATE(SUM(Sales[Sales]),FILTER(Sales,MONTH(Sales[OrderDate])=MONTH(TODAY())-1))/CALCULATE(SUM(Stock[Stock]),FILTER(Stock,MONTH(Stock[Date])=MONTH(TODAY())))*30
Thank you,
This helped me a lot. I wonder if there is a way to calculate days of stock of months in the past.
For example:
Days of Stock of November would be calculated
DAYS OF STOCK= Sales of October/Stock of November.
Because this measure only works with current month. I've been trying to this but I can't figure out a way.
Hope you can help me.
@Anonymous wrote:
Thank you,
This helped me a lot. I wonder if there is a way to calculate days of stock of months in the past.
For example:
Days of Stock of November would be calculated
DAYS OF STOCK= Sales of October/Stock of November.
Because this measure only works with current month. I've been trying to this but I can't figure out a way.
Hope you can help me.
Here we can create a date table and use SELECTEDVALUE to work on it. Could you please share your sample data to me? Please upload your files to One Drive and share the link here.
Regards,
Frank
Hi, this file contains a sample of 4 last months.
https://m8p-my.sharepoint.com/:x:/g/personal/abisai_perez_moksha8_com/EZtGunA8WIZOhS3M_WThncMB5CFQAx...
There are two tables, one is for sales and the other is for stock.
I'm trying to get days of stock, calculated this way
Days of stock=(current month stock/previous month sales)*30
I also have a date table.
Hope you can help me.
Thank you.
Hi @Anonymous ,
As your description, I use the measure to achieve to get the result Days of stock=(current month sales/previous month stock)*30. As in your table, there is no data for stock for Feb 2019.
Days of stock = VAR ym = YEAR ( TODAY () ) * 100 + MONTH ( TODAY () ) VAR pm = YEAR ( EDATE ( TODAY (), -1 ) ) * 100 + MONTH ( EDATE ( TODAY (), -1 ) ) VAR curentamount = CALCULATE ( SUM ( Sales[UNIDADES] ), FILTER ( Sales, Sales[Año] * 100 + Sales[Mes] = ym ) ) VAR previousamount = CALCULATE ( SUM ( Stock[PZAS] ), FILTER ( Stock, 'Stock'[AÑO] * 100 + Stock[MES] = pm ) ) RETURN curentamount / previousamount * 30
Also please find the pbix as attached.
Regards,
Frank
@Anonymous I would suggest to replace Today() with required date . Remember its not dynamic. If you want to change this dynamically you need to modify the logic.
Days Of Stock2 = CALCULATE(SUM(Sales[Sales]),FILTER(Sales,MONTH(Sales[Date])=MONTH(DATE(2018,11,30)-1)))/CALCULATE(SUM(Stock[Stock]),FILTER(Stock,MONTH(Stock[StartDate])=MONTH(DATE(2018,12,31))))*30
User | Count |
---|---|
139 | |
113 | |
103 | |
73 | |
63 |
User | Count |
---|---|
136 | |
125 | |
107 | |
70 | |
61 |