Get help with getting a rolling month bar graph

[Anonymous]

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 based on the end of that month.

For example for month of April, it should only 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 should be apply to all other month, Is this possible to be showed within a month bar visual?

Thankyou!

[Anonymous]

Hi @Anonymous ,

According to your description, here are my steps you can follow as a solution.

(1) This is my test data. 

(2) We can create  a calculated column.

Month = MONTH('Table'[approve date])

(3)We can create  a measure. 

Count = 

var _appro   =SELECTEDVALUE('Table'[Month])

var _t= FILTER( 'Table' , MONTH('Table'[deployment date]) = _appro+1)

return

COUNTROWS(_t)

(4) Then the result is as follows.

If the above one can't help you get the desired result, please provide some sample data in your tables (exclude sensitive data) with Text format and your expected result with backend logic and special examples. It is better if you can share a simplified pbix file. Thank you.

Best Regards,

Neeko Tang

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly. 

[Anonymous]

Hi i have got the answer, but it only count the deployment month one month after the approve month, it does not count deployment month more month after the approve month.

for example it only count the project on jan when the deployment month is feb, but if it is mar, it is not counted, is there a way to solve it?

[Anonymous]

Hi @Anonymous ,

We can create  a measure. 

Measure = 
var _a = EOMONTH(MAX('Table'[approve date]),0)
return CALCULATE(COUNT('Table'[project]),FILTER(ALL('Table'),[approve date]<=_a&&[deployment date]>_a))

Then the result is as follows.

Best Regards,

Neeko Tang

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly. 

[Anonymous]

i realise i edited the formula correctly but the value is wrong

[Anonymous]

 this was the error

[Anonymous]

hi i can only get it for the year, but not the month, the month it will show error