Cumulative Data by Unique Value

Hi everyone, I would greatly appreciate if you can assist me with a PBI problem I currently have. Here is the breakdown:

Context & Problem:
I have a very large data set that includes Baseline, Actual and Forecast dates by Projects (the dataset is well over 22,000 lines). What I am trying to is to chart the following:

  1. Bar chart showing count of Baseline, Actual and Forecast dates filterable by Project ID.
  2. Show cumulative count of Baseline, Actual and Forecast dates filterable by Project ID.

I am trying to show this in a chart where you have 2 y-axis (one for period count and second one for cumulative count) and the period on x-axis (Jan, Feb, etc). Here is a quick example:

I was able to do the monthly count filterable by project id with extra manual steps in excel and load that data into PBI. However, I am struggling to do the cumulative data filterable by project ID. Also, I would like to do all of this in PBI rather than additional pre-processing steps in excel.

I have attached a sample file that includes data, calculation and the graph. Please note that I do not have the project filter on this file.

Cumulative Data.xlsx (29.6 KB)

I would greatly appreciate if you can help me build this. Thank you in advance :).


Hello @tt.xavier,

Thank You for posting your query onto the Forum.

You can achieve this result by creating inactive relationships with the Date table and then virtually activating them by using the USERELATIONSHIP() function. Below is the screenshot provided for the reference about how the data model looks like -

After creating the inactive relationships I’ve used the USRELATIONSHIP() function to achieve the required result. Below is the screenshot provided of the end result -

I’m attaching the Excel as well as the PBIX file of my working for the reference. Also I’m providing a link of a video of the function that I’ve used which is available on our education and I’m sure there are many more videos available on the portal covering this concept.

Hoping you find this useful and meets your requirements. :slightly_smiling_face:

Thanks & Warm Regards,

Cumulative Data.xlsx (30.5 KB)

Cumulative Data.pbix (95.6 KB)

Hi Harsh,

Thank so much for getting back to me quickly and for the solution which works perfectly. Much appreciate it. Have a great day.


Hello @tt.xavier,

You’re Welcome. :slightly_smiling_face:

I’m glad I was able to help you.

Thanks & Warm Regards,