Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
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!
Solved! Go to Solution.
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
Proud to be a Super User!
Check out my blog: Power BI em Português
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:
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:
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:
Hope this helps!
Proud to be a Super User!
Paul on Linkedin.
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:
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:
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:
Hope this helps!
Proud to be a Super User!
Paul on Linkedin.
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
Proud to be a Super User!
Check out my blog: Power BI em PortuguêsHi MFelix.
It works perfectly.
Thanks a lot my friend!.
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
User | Count |
---|---|
140 | |
113 | |
104 | |
77 | |
65 |
User | Count |
---|---|
136 | |
118 | |
101 | |
71 | |
61 |