Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Anonymous
Not applicable

Looking help for date bar visual

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?

JJ0802_0-1667979715248.png

this is the dummy data set and what the outcome should looks like.

Thankyou for the help!

1 ACCEPTED SOLUTION
v-jingzhang
Community Support
Community Support

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. 

vjingzhang_0-1668065620390.png

 

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. 

vjingzhang_1-1668065821194.png

 

Output: 

vjingzhang_2-1668065858348.png

 

You can modify colors via

vjingzhang_3-1668065963116.png

 

Best Regards,
Community Support Team _ Jing
If this post helps, please Accept it as Solution to help other members find it.

View solution in original post

2 REPLIES 2
v-jingzhang
Community Support
Community Support

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. 

vjingzhang_0-1668065620390.png

 

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. 

vjingzhang_1-1668065821194.png

 

Output: 

vjingzhang_2-1668065858348.png

 

You can modify colors via

vjingzhang_3-1668065963116.png

 

Best Regards,
Community Support Team _ Jing
If this post helps, please Accept it as Solution to help other members find it.

Anonymous
Not applicable

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

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.