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	CUST REQ DATE	EST. FORECAST DELIVERY
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

Expecting outcome is as below

[v-jianboli-msft · Accepted solution]

Hi @Vinod_P ,

Please try:

First create a calendar table:

Then create a matrix visual and apply these measures:

Demand = CALCULATE(COUNT('Table'[Part number]),FILTER('Table',[CUST REQ DATE]<=EOMONTH(MAX('Calendar'[Date]),0)&&[CUST REQ DATE]>EOMONTH(MAX('Calendar'[Date]),-1)))

Supply = CALCULATE(COUNT('Table'[Part number]),FILTER('Table',[EST. FORECAST DELIVERY]<=EOMONTH(MAX('Calendar'[Date]),0)&&[EST. FORECAST DELIVERY]>EOMONTH(MAX('Calendar'[Date]),-1)))

LOB = [Demand]-[Supply]

Switch values to rows:

Final output:

Best Regards,

Jianbo Li

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Vinod_P]

Hi , 

Thanks a lot for the solution. Need one more small add to the below report

Want to add Opening stock = previous month closing ,  for the first month it will be "0" 

Below screenshot FYR

Please help