cancel
Showing results for
Did you mean:
Regular Visitor

## Measure that divides total of a month by the total of the next month

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)*30`
Hope you can help me.
Thank you.
1 ACCEPTED SOLUTION

Accepted Solutions
Community Support Team

## Re: Measure that divides total of a month by the total of the next month

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 (
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

Community Support Team _ Frank
If this post helps, then please consider Accept it as the solution to help the others find it more quickly.
8 REPLIES 8
Super User IV

## Re: Measure that divides total of a month by the total of the next month

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...

### I have book! Learn Power BI from Packt

Proud to be a Datanaut!

Regular Visitor

## Re: Measure that divides total of a month by the total of the next month

Very good information but still I can't solve my issue.

Member

## Re: Measure that divides total of a month by the total of the next month

@abisaileon11

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`

Regular Visitor

## Re: Measure that divides total of a month by the total of the next month

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.

Member

## Re: Measure that divides total of a month by the total of the next month

@abisaileon11 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`
Community Support Team

## Re: Measure that divides total of a month by the total of the next month

@abisaileon11 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

Community Support Team _ Frank
If this post helps, then please consider Accept it as the solution to help the others find it more quickly.
Regular Visitor

## Re: Measure that divides total of a month by the total of the next month

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.

Community Support Team

## Re: Measure that divides total of a month by the total of the next month

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 (
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

Community Support Team _ Frank
If this post helps, then please consider Accept it as the solution to help the others find it more quickly.

Announcements

#### Meet the 2020 Season 1 Power BI Super Users!

It’s the start of a new Super User season! Learn all about the new Super Users and brand-new tiered recognition system.

#### Super User Challenge: Can You Solve These?

We're celebrating the start of the New Super User season with our first ever Super User 'Can You Solve These?' challenge.

#### January 2020 Community Highlights

Make sure you didn't miss any of the things that happened in the community in January!

Top Solution Authors
Top Kudoed Authors