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
JuanVR11
Helper I
Helper I

accumulated sum against target in other table

Hi friends.

 

i have a table/list ("Stock") that contains two columns, "date" and "qty" with quantity of stock for date.

 

Date              |      Quantity

20200103                       20

20200105                     100

20200115                     350

20200206                      54

20200218                     140

20200221                     210

20200224                     100

20200310                       80

20200315                      120

 

There´s another table ("Plan") that contains the plan of stocks for month

 

Month                  |       Stock

20200101                        600

20200201                       1400

20200301                         800

 

I want to create a third table with the quantity of stock to reach the Plan for Month. for example:

 

Month                 |         Rest

20200101                       130

20200201                       896 

20200301                       600

 

I really hope u can help me.

 

Thanks!

 

 

2 ACCEPTED SOLUTIONS
MFelix
Super User
Super User

Hi @JuanVR11 ,

 

You can create a calendar table and make the relationship between both tables then you just need to create the following measure:

 

Rest = SUM(Plan[Stock])- SUM(Stock[Quantity])

 

You can also make a MTD if you want to calculated the total values per day.

By day = TOTALMTD(SUM(Plan[Stock]);'Calendar'[Date])- TOTALMTD(SUM(Stock[Quantity]);'Calendar'[Date])

 

Check PBIX file attach.


Regards

Miguel Félix


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

Proud to be a Super User!

Check out my blog: Power BI em Português



View solution in original post

PaulDBrown
Community Champion
Community Champion

@JuanVR11 

 

First set up the model to include a month table. To do this go into Power Query and:

1) select each of your tables and add a column in each (I used column by examples) to create your Month Ref:

Month Ref 1.JPGMonth Ref 2.JPG

2) Create a new table (Month Table) by referencing to one of your tables, removing all columns except the "Month Ref" column. Remove duplicate rows.

3) load your tables into your model and establish the following structure, joining the Month Table to your other tables by joining the "Month Ref" columns in a one-to-many relationship. It should look like this:

model.JPG

 

4) Create your measures:

 

 

Sum of Planned = SUM(PlannedStock[Planned Stock])
Sum of Stock = SUM(Stock[Quantity])
To reach Planned = [Sum of Planned]-[Sum of Stock]

 

Now creat your table using the "Month Ref" column in your Month Table as your field and add the measure to get this:

Result.JPG

 

Hope this helps!

 





Did I answer your question? Mark my post as a solution!
In doing so, you are also helping me. Thank you!

Proud to be a Super User!
Paul on Linkedin.






View solution in original post

4 REPLIES 4
PaulDBrown
Community Champion
Community Champion

@JuanVR11 

 

First set up the model to include a month table. To do this go into Power Query and:

1) select each of your tables and add a column in each (I used column by examples) to create your Month Ref:

Month Ref 1.JPGMonth Ref 2.JPG

2) Create a new table (Month Table) by referencing to one of your tables, removing all columns except the "Month Ref" column. Remove duplicate rows.

3) load your tables into your model and establish the following structure, joining the Month Table to your other tables by joining the "Month Ref" columns in a one-to-many relationship. It should look like this:

model.JPG

 

4) Create your measures:

 

 

Sum of Planned = SUM(PlannedStock[Planned Stock])
Sum of Stock = SUM(Stock[Quantity])
To reach Planned = [Sum of Planned]-[Sum of Stock]

 

Now creat your table using the "Month Ref" column in your Month Table as your field and add the measure to get this:

Result.JPG

 

Hope this helps!

 





Did I answer your question? Mark my post as a solution!
In doing so, you are also helping me. Thank you!

Proud to be a Super User!
Paul on Linkedin.






MFelix
Super User
Super User

Hi @JuanVR11 ,

 

You can create a calendar table and make the relationship between both tables then you just need to create the following measure:

 

Rest = SUM(Plan[Stock])- SUM(Stock[Quantity])

 

You can also make a MTD if you want to calculated the total values per day.

By day = TOTALMTD(SUM(Plan[Stock]);'Calendar'[Date])- TOTALMTD(SUM(Stock[Quantity]);'Calendar'[Date])

 

Check PBIX file attach.


Regards

Miguel Félix


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

Proud to be a Super User!

Check out my blog: Power BI em Português



Hi MFelix.

 

It works perfectly.

Thanks a lot my friend!.

 

 

amitchandak
Super User
Super User

Create a common date table.  One table has date second table has first date of the month.

Using this common dim you can check (New measure)

rest = sum(tableB[Stock]) - sum(TableA[Qty])

 

Appreciate your Kudos. In case, this is the solution you are looking for, mark it as the Solution. In case it does not help, please provide additional information and mark me with @
Thanks. My Recent Blog -
Winner-Topper-on-Map-How-to-Color-States-on-a-Map-with-Winners , HR-Analytics-Active-Employee-Hire-and-Termination-trend
Power-BI-Working-with-Non-Standard-Time-Periods And Comparing-Data-Across-Date-Ranges

Connect on Linkedin

 

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.