Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
14 | |
2 | |
1 | |
1 | |
1 |
User | Count |
---|---|
25 | |
3 | |
2 | |
2 | |
2 |