Supply vs demand

[Vinod_P]

Below is the sameple data and looking for demand( Count of values in demand date column) & Supply (Count of values in the supply date column) , Looking at a view to compare both counts like 1-1 against the fixed jan to dec month on X-axis.

ID NUMBER	Part number	CUSTOMER	DEMAND DATE	SUPPLY DATE
1	1	Vinod	17/08/2022	12/01/2023
2	1	Vinod	17/08/2022	04/05/2023
3	1	Vinod	17/08/2022	27/01/2023
4	1	Vinod	17/08/2022	05/04/2023
5	2	Vinod	01/02/2022	20/01/2023
6	2	Vinod	01/02/2022	20/01/2023
7	2	Vinod	01/02/2022	20/01/2023
8	2	Vinod	01/02/2022	20/01/2023
9	2	Vinod	01/02/2022	10/03/2023
10	2	Vinod	01/02/2022	28/02/2023
11	2	Vinod	01/02/2022	10/03/2023
12	2	Vinod	01/02/2022	05/04/2023
13	2	Vinod	27/04/2022	20/01/2023
14	1	Vinod	21/10/2022	05/04/2023
15	1	Vinod	21/10/2022	05/04/2023
16	1	Vinod	21/10/2022	17/04/2023
17	1	Vinod	21/10/2022	04/05/2023
18	1	Vinod	21/10/2022	17/04/2023

Expected outcome

Previous month closing stock should be added to the current 

LOB = Current month (Supply-Demand) + Previous month (LOB) 

[amitchandak]

@Vinod_P , You have to create cumulative here. Also because both are in same table you will have active/inactive join when joining with a common date table

Assume demand date join is inactive when you join both dates with same date table

CALCULATE(Count(Table[Supply Date]),filter(date,date[date] <=maxx(date,date[date]))) -

CALCULATE(CALCULATE(Count(Table[Demand Date]),USERELATIONSHIP( Date[Date], Table[Demand Date] ))

filter(date,date[date] <=maxx(date,date[date])))

Date Table

Date = Addcolumns(calendar(date(2012,01,01), date(2024,12,31) ), "Month no" , month([date])
, "Year", year([date])
, "Month Year", format([date],"mmm-yyyy")
, "Month year sort", year([date])*100 + month([date])
, "Qtr Year", format([date],"yyyy-\QQ")
, "Qtr", quarter([date])
, "Month",FORMAT([Date],"mmmm")
, "Month sort", month([DAte])
, "FY Year", if( Month(_max) <7 , year(_max)-1 ,year(_max))
, "Is Today" ,if([Date]=TODAY(),"Today",[Date]&"")
,"Day of Year" , datediff(date(year([DAte]),1,1), [Date], day)+1
, "Month Type", Switch( True(),
eomonth([Date],0) = eomonth(Today(),-1),"Last Month" ,
eomonth([Date],0)= eomonth(Today(),0),"This Month" ,
Format([Date],"MMM-YYYY") )
,"Year Type" , Switch( True(),
year([Date])= year(Today()),"This Year" ,
year([Date])= year(Today())-1,"Last Year" ,
Format([Date],"YYYY")
)
)

refer for related concepts

Active/Inactive date

Power BI: HR Analytics - Employees as on Date : https://youtu.be/e6Y-l_JtCq4
https://community.powerbi.com/t5/Community-Blog/HR-Analytics-Active-Employee-Hire-and-Termination-tr...

Power BI Inventory On Hand: https://youtu.be/nKbJ9Cpb-Aw

[Vinod_P]

Hi Amit, 

Thanks for the quick response. 

I managed to get supply & demand formauls

Supply = if(CALCULATE(COUNT(Tracker[SEQ ID]), FILTER(Tracker,Tracker[EST. FORECAST DELIVERY]<=EOMONTH(max('Date'[Date]),0) && Tracker[EST. FORECAST DELIVERY]>EOMONTH(max('Date'[Date]),-1)))=BLANK(),0,CALCULATE(COUNT(Tracker[SEQ ID]), FILTER(Tracker,Tracker[EST. FORECAST DELIVERY]<=EOMONTH(max('Date'[Date]),0) && Tracker[EST. FORECAST DELIVERY]>EOMONTH(max('Date'[Date]),-1))))

and 

Demand = if(CALCULATE(COUNT(Tracker[SEQ ID]), FILTER(Tracker,Tracker[CUST REQ DATE]<=EOMONTH(max('Date'[Date]),0) && Tracker[CUST REQ DATE]>EOMONTH(max('Date'[Date]),-1)))=BLANK(),0,CALCULATE(COUNT(Tracker[SEQ ID]), FILTER(Tracker,Tracker[CUST REQ DATE]<=EOMONTH(max('Date'[Date]),0) && Tracker[CUST REQ DATE]>EOMONTH(max('Date'[Date]),-1))))

and LOB = supply- demand 

But I am getting struck at adding previous month closing stock to the LOB . Could you please help in formula for considering the previous month closing stock