Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Anonymous
Not applicable

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

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

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
Greg_Deckler
Super User
Super User

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


@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
Mastering Power BI 2nd Edition

DAX is easy, CALCULATE makes DAX hard...
Anonymous
Not applicable

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

itsmebvk
Continued Contributor
Continued Contributor

@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

 

Anonymous
Not applicable

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

 

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

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

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.
itsmebvk
Continued Contributor
Continued Contributor

@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

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.