Forum Discussion

Vinod_P's avatar
Vinod_P
Helper I
3 years ago
Solved

Supply vs demand

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 NUMBERPart numberCUSTOMERCUST REQ DATEEST. FORECAST DELIVERY
11Vinod 17/08/202212/01/2023
21Vinod 17/08/202204/05/2023
31Vinod 17/08/202227/01/2023
41Vinod 17/08/202205/04/2023
52Vinod 01/02/202220/01/2023
62Vinod 01/02/202220/01/2023
72Vinod 01/02/202220/01/2023
82Vinod 01/02/202220/01/2023
92Vinod 01/02/202210/03/2023
102Vinod 01/02/202228/02/2023
112Vinod 01/02/202210/03/2023
122Vinod 01/02/202205/04/2023
132Vinod 27/04/202220/01/2023
141Vinod 21/10/202205/04/2023
151Vinod 21/10/202205/04/2023
161Vinod 21/10/202217/04/2023
171Vinod 21/10/202204/05/2023
181Vinod 21/10/202217/04/2023

Expecting outcome is as below

 

Need Help
Show and Tell
  • v-jianboli-msft's avatar
    v-jianboli-msft
    3 years ago

    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.

2 Replies

  • v-jianboli-msft's avatar
    v-jianboli-msft
    Community Support
    3 years ago

    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's avatar
      Vinod_P
      Helper I
      3 years ago

      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