Forum Discussion
gardas_swathi
Microsoft Employee
Microsoft EmployeeCalculate Beginning Quarter Value
I need to calculate beginning inventory based on the quarter selection
| CalendarDate | FiscalQuarterDesc | FiscalYearNbr | FiscalMonthName | CalendarYear |
| 7/1/2019 | FY20-Q1 | FY20 | FY20-Jul | 2019 |
| 8/1/2019 | FY20-Q1 | FY20 | FY20-Aug | 2019 |
| 9/1/2019 | FY20-Q1 | FY20 | FY20-Sep | 2019 |
| 10/1/2019 | FY20-Q2 | FY20 | FY20-Oct | 2019 |
| 11/1/2019 | FY20-Q2 | FY20 | FY20-Nov | 2019 |
| 12/1/2019 | FY20-Q2 | FY20 | FY20-Dec | 2019 |
| 1/1/2020 | FY20-Q3 | FY20 | FY20-Jan | 2020 |
| 2/1/2020 | FY20-Q3 | FY20 | FY20-Feb | 2020 |
| 3/1/2020 | FY20-Q3 | FY20 | FY20-Mar | 2020 |
| 4/1/2020 | FY20-Q4 | FY20 | FY20-Apr | 2020 |
| 5/1/2020 | FY20-Q4 | FY20 | FY20-May | 2020 |
| 6/1/2020 | FY20-Q4 | FY20 | FY20-Jun | 2020 |
| Value | Date | ProductID |
| 3000 | 7/1/2019 | 1 |
| 4000 | 7/1/2019 | 2 |
| 2000 | 8/1/2019 | 1 |
| 1000 | 9/1/2019 | 1 |
| 5000 | 10/1/2019 | 1 |
| 4000 | 11/1/2019 | 1 |
| 200 | 12/1/2019 | 1 |
| 9000 | 1/1/2020 | 1 |
| 5000 | 2/1/2020 | 1 |
| 600 | 3/1/2020 | 1 |
| 4500 | 4/1/2020 | 1 |
| 2500 | 5/1/2020 | 1 |
| 300 | 6/1/2020 | 1 |
If the Users Chooses Q1 with no filter on product , Beginning Inventory for Q1 should be 7000
Beginning inventory when user filters on Fiscal quarter Q2 , it should change to 5000.
How do i acheive this through DAX ?
Thanks in Advance
1 Reply
first load both table in power Bi.
Create below calciulated column
First_month_of_Quarter = CALCULATE(MIN(Sheet5[CalendarDate]),ALLEXCEPT(Sheet5,Sheet5[FiscalQuarterDesc]))
Ten go to relationship tab and create relatiomn on First_month_of_Quarter to second date table.(Many to many)
Create measure
Starting_Inventory = SUM(Sheet4[Value])and finally drag this measure and date column in table it will show expect result.Thanks & regards,
Pravin Wattamwar
www.linkedin.com/in/pravin-p-wattamwar
If I resolve your problem Mark it as a solution and give kudos.
