cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
abisaileon11 Regular Visitor
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
Community Support Team

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

Hi @abisaileon11 ,

 

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

Capture.PNG

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.

View solution in original post

8 REPLIES 8
Super User IV
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


Did I answer your question? Mark my post as a solution!

Proud to be a Datanaut!

abisaileon11 Regular Visitor
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. 

itsmebvamsi Member
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

 

abisaileon11 Regular Visitor
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. 

 

itsmebvamsi Member
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
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.
abisaileon11 Regular Visitor
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
Community Support Team

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

Hi @abisaileon11 ,

 

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

Capture.PNG

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.

View solution in original post

Helpful resources

Announcements
Exclusive LIVE Community Event #3 – Ask Arun Anything

Exclusive LIVE Community Event #3 – Ask Arun Anything

Join us in the third Triple A event!

Meet the 2020 Season 1 Power BI Super Users!

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?

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

January 2020 Community Highlights

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

Top Solution Authors