Calculate Beginning Quarter Value

[gardas_swathi]

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

[Anonymous]

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.