Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
Sorry, this might be the thrid similar question because i have accidentally accepted the soultion and i dont know how to get it back
The question is :
There are multiple projects, and they have different approve date and deployment date, i want to achieve a date bar graph which each month will show the number of on going projects.
for example for month of April, it should onlu display the count of projects which approve date is before the last day of april (30th april) and the deployment date of the project is on or after the first day of next month (1st may)
This is only for april, and this calcualtion apply to all other month, and i want them to show on the same visual. Is this possible?
this is the dummy data set and what the outcome should looks like.
Thankyou for the help!
Solved! Go to Solution.
Hi @Anonymous
Please follow my steps:
First add a Date table to your model with DAX:
Date = ADDCOLUMNS(CALENDAR(DATE(2022,1,1),DATE(2022,12,31)),"Month",FORMAT([Date],"mmm"),"Month Number",MONTH([Date]))
Select Month column and sort it by Month Number column.
Then create a measure
Number of ongoing projects = COUNTX(FILTER('Table','Table'[Approval Date] < MAX('Date'[Date]) && 'Table'[Deployment Date] >= MAX('Date'[Date]) + 1), 'Table'[Project code]) + 0
Add Date table's Month column to X-axis of the column chart and add above measure to Y-axis. Turn on Data labels option in Format pane.
Output:
You can modify colors via
Best Regards,
Community Support Team _ Jing
If this post helps, please Accept it as Solution to help other members find it.
Hi @Anonymous
Please follow my steps:
First add a Date table to your model with DAX:
Date = ADDCOLUMNS(CALENDAR(DATE(2022,1,1),DATE(2022,12,31)),"Month",FORMAT([Date],"mmm"),"Month Number",MONTH([Date]))
Select Month column and sort it by Month Number column.
Then create a measure
Number of ongoing projects = COUNTX(FILTER('Table','Table'[Approval Date] < MAX('Date'[Date]) && 'Table'[Deployment Date] >= MAX('Date'[Date]) + 1), 'Table'[Project code]) + 0
Add Date table's Month column to X-axis of the column chart and add above measure to Y-axis. Turn on Data labels option in Format pane.
Output:
You can modify colors via
Best Regards,
Community Support Team _ Jing
If this post helps, please Accept it as Solution to help other members find it.
Additional editon on the code after trying:, this should be working fine to include the last day on the approve month too!
Number of ongoing projects = COUNTX(FILTER('Table','Table'[Approval Date] <= MAX('Date'[Date]) && 'Table'[Deployment Date] >= MAX('Date'[Date]) + 1), 'Table'[Project code]) + 0
User | Count |
---|---|
140 | |
113 | |
104 | |
77 | |
65 |
User | Count |
---|---|
136 | |
118 | |
101 | |
71 | |
61 |