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 again...
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.

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