Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
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
Solved! Go to 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.
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.
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
User | Count |
---|---|
13 | |
4 | |
2 | |
1 | |
1 |
User | Count |
---|---|
22 | |
3 | |
3 | |
2 | |
2 |