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
Jaromir
Frequent Visitor

Visualization: stock differences between days

Hi there,
I have a database with products (quantity in stock) and their changes in time and I need a graph with differences between days.

The table can look like this:

2017-02-07 23_24_05-amount- Power BI Desktop.png

 

I don't know how to calculate differences between days (in the picture is shown just two days but there will be each day of a month) and visualize them.

 

I will really appriciate any help. Thanks a lot!

 

 

 

 

DB structure

2017-02-07 23_25_11-structure- Power BI Desktop.png

1 ACCEPTED SOLUTION

Hi @Jaromir,

 

You could create calculated measure for "Total sales to previous date":

 

Prev Sales = CALCULATE(SUM(Sales[Sales]),PREVIOUSDAY(Sales[Date]))

Create calculated measure for stock differences between days:

 

stock differences between days = sum(Sales[Sales])- [Prev Sales]

 

 

Screenshot 2017-02-08 13.43.16.png

 

If this works for you please accept it as solution and also like to give KUDOS.

Best regards
Tri Nguyen

View solution in original post

5 REPLIES 5
tringuyenminh92
Memorable Member
Memorable Member

Hi @Jaromir,

 

What is meaning of differences between days, could you show me 1 example for that logic? so i could understand it correctly.

Hi,

I basically need to calculate a difference in stock amount for a selected product_id between two consecutive dates.

 

Say the stock amount on Feb 1 is 43, on Feb 2 it is 38. I need to add a column which would calculate that the difference in stock between those two days is -5. And I need to do the same for every row (date).

 

Thanks!

Hi @Jaromir,

 

You could create calculated measure for "Total sales to previous date":

 

Prev Sales = CALCULATE(SUM(Sales[Sales]),PREVIOUSDAY(Sales[Date]))

Create calculated measure for stock differences between days:

 

stock differences between days = sum(Sales[Sales])- [Prev Sales]

 

 

Screenshot 2017-02-08 13.43.16.png

 

If this works for you please accept it as solution and also like to give KUDOS.

Best regards
Tri Nguyen

Hi @tringuyenminh92,

 

thanks for your response. I have created a calculated measure as you suggested:

 

PrevStock = CALCULATE(SUM('data'[stock]); PREVIOUSDAY('data'[date]))

But it only returns empty field. I have checked and there is a row with a previous day date. What could I be doing wrong?

 

Thanks again.

I found the problem, had to use PREVIOUSDAY('data'[date].[Date]).

 

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.