Cumulative Data by Unique Value

Power BI
1

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:

image
image1054×387 16.8 KB

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 :).

Cheers

2

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 -

Cumulative Data - 1
Cumulative Data - 1817×560 38.1 KB

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 -

Cumulative Data - 2
Cumulative Data - 21323×749 132 KB

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,
Harsh

Cumulative Data.xlsx (30.5 KB)

Cumulative Data.pbix (95.6 KB)

3

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.

Cheers

4

Hello @tt.xavier,

You’re Welcome. :slightly_smiling_face:

I’m glad I was able to help you.

Thanks & Warm Regards,
Harsh