Cumulative completed projects per month/year Line Chart

[Anonymous]

Hi all,

I am relativily new to Power BI and have been trying to reproduce something I made in Excel, see image below:

I saw a similar post (https://community.powerbi.com/t5/Desktop/Compare-cumulative-data-between-years/td-p/112480) that has a solution but I still can't manage to do it and would really appriciate some step by step help here.

Thank you in advance!

Regards, Stefan

[Gabriel_Walkman]

Hi!

What does your source table look like? What did you try and how did it fail?

[Anonymous]

Hi Gabriel,

Appreciate your reply, I uploaded my source table and how it fails in my other comment.

[amitchandak]

@Anonymous , 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.

[Anonymous]

@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!