Forum Discussion
Cumulative completed projects per month/year Line Chart
Super UserAnonymous , Create a date table with Year and Month and month sort.
and use a formula like this
YTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESYTD('Date'[Date],"12/31"))
Month on axis , year as legend
column in date table
Month= FORMAT([Date],"mmm")
Month sort = month([Date])
To get the best of the time intelligence function. Make sure you have a date calendar and it has been marked as the date in model view. Also, join it with the date column of your fact/s. Refer :radacad sqlbi My Video Series Appreciate your Kudos.
amitchandak Thank you for your quick reply, I did some of the steps you mentioned and still don't get the desired result, probably because I'm still doing something wrong.
Here is what I did:
My raw input test date for this forum
Project ID Project Completion Date
1 1-Jan-20
2 1-Feb-20
3 1-Feb-20
4 1-Feb-20
5 1-Feb-20
6 1-Feb-20
7 1-Feb-20
8 1-Feb-20
9 1-Mar-20
10 1-Apr-20
11 1-Apr-20
12 1-May-20
13 2-May-20
14 3-May-20
15 4-May-20
16 1-Jun-20
17 1-Jun-20
18 1-Jul-20
19 1-Aug-20
20 1-Sep-20
21 1-Oct-20
22 1-Oct-20
23 1-Oct-20
23 2-Oct-20
25 3-Oct-20
26 4-Oct-20
27 1-Nov-20
28 1-Dec-20
29 1-Jan-21
30 2-Jan-21
31 3-Jan-21
32 4-Jan-21
33 1-Feb-21
34 1-Mar-21
35 1-Apr-21
36 1-May-21
37 2-May-21
38 3-May-21
39 4-May-21
40 1-Jun-21
41 2-Jun-21
42 3-Jun-21
43 4-Jun-21
44 1-Jul-21
45 2-Jul-21
46 3-Jul-21
47 4-Jul-21
48 1-Aug-21
49 1-Aug-21
50 1-Sep-21
51 1-Oct-21
52 1-Nov-21
53 1-Dec-21
54 1-Dec-21
55 1-Jan-22
Then I created a data calendar like in the sqlbi post you shared (thanks!):
As I don't have a 'Sales Amount' that I want to SUM but a (distinct) Count of the completed projects, this is the Measure that I came up with:
YTDprojects = CALCULATE(DISTINCTCOUNT(Sheet1[Project ID]),DATESYTD('Date'[Date],"12/31"))
I think that part is where it goes wrong giving me the following result:
Thanks again for your help and please let me know what I'm doing wrong!