Develop cumulative measures and manage the stacked column chart using only one date column.

[RiccardoRocchi]

Hi everyone,

Today, I would like to ask for your suggestions on how to solve the following issue. I have a table containing some demographic information, along with three date columns (Date Planned, Date Expected, and Date Actual) and three quantitative columns (Activity Planned, Activity Expected, and Activity Actual).

The goal is to create a stacked column chart that shows the cumulative values for Activity Planned, Activity Expected, and Activity Actual (i.e., three different cumulative measures), divided by quarters.

However, I need to manage different scenarios: I require a slicer to analyze, for instance, the cumulative values for a single year or for multiple years. In such cases, whenever I select only one year, the cumulative values should start from the first quarter of that year, rather than from previous years. The same logic applies for two or more years. For example, if my database contains data from 2020 to 2024 and I want to view the cumulative values for 2022 and 2023, the chart should display the data starting from the first quarter of 2022 and continue cumulatively until the end of 2023.

I attempted to address this by splitting the main table into three separate tables (Expected, Actual, Planned) and establishing a one-to-many relationship between each corresponding date and the Calendar table's date (via CALENDARAUTO()).

I am able to correctly display the cumulated data starting from the first quarter for a single year excluding the previous years from the filter page (i.e., when I select only one year, the cumulative values start from the first quarter of the selected year). However, when I select multiple years, the measures compute the cumulative values by including all previous years. I can achieve the desired result by applying a page filter, but since I need to manage three different measures, I have to manually adjust the Date Actual, Date Expected, and Date Planned filters each time.
Here is one of the measures I created to calculate the cumulative values:

Cumulative Sum Act =
CALCULATE(
SUM('Actual'[Activity Actual]),
FILTER(
ALL('Calendar'[Date]),
'Calendar'[Date] <= MAX('Calendar'[Date])
)
)

I would appreciate any advice on how to manage all the measures with a single date (without relying on the page filter) so that the chart displays the cumulative values for the selected years, starting from the first quarter of the earliest selected year.

Thank you very much!

[v-jialongy-msft]

Hi @RiccardoRocchi 

You can try to follow the following steps to achieve your needs:

1. Create a Calendar Table

You should also include a column for Quarter-Year in this table, which will help in dividing the data by quarters.

Calendar =
ADDCOLUMNS(
CALENDARAUTO(),
"Year", YEAR([Date]),
"Quarter", "Q" & QUARTER([Date]),
"YearQuarter", YEAR([Date]) & "Q" & QUARTER([Date])
)

2.Create Cumulative Measures

You will need to modify the cumulative measures for each activity type (Planned, Expected, and Actual) to reset based on the slicer selection. Here's an example for the Activity Planned measure:

Cumulative Activity Planned =
CALCULATE(
SUM(YourTable[Activity Planned]),
FILTER(
ALLSELECTED(Calendar),
Calendar[Date] <= MAX(Calendar[Date]) &&
Calendar[Year] >= MIN(Calendar[Year])
)
)

3.Repeat the Cumulative Measure for Other Columns

Similarly, you will create cumulative measures for Activity Expected and Activity Actual:

Cumulative Activity Expected =
CALCULATE(
SUM(YourTable[Activity Expected]),
FILTER(
ALLSELECTED(Calendar),
Calendar[Date] <= MAX(Calendar[Date]) &&
Calendar[Year] >= MIN(Calendar[Year])
)
)

Cumulative Activity Actual =
CALCULATE(
SUM(YourTable[Activity Actual]),
FILTER(
ALLSELECTED(Calendar),
Calendar[Date] <= MAX(Calendar[Date]) &&
Calendar[Year] >= MIN(Calendar[Year])
)
)

4.Stacked Column Chart

Once you have the cumulative measures, you can use a Stacked Column Chart to plot the cumulative values by YearQuarter. This will show the cumulative sums split by quarters, respecting the slicer selection.

Axis: 'Calendar[YearQuarter]'

Values: Add ‘Cumulative Activity Planned’, ‘Cumulative Activity Expected’, and ‘Cumulative Activity Actual’.

5.Slicer for Years

Add a slicer based on the ‘Calendar[Year] ‘column. This will allow users to select one or multiple years, and the cumulative values will automatically reset and start from the first quarter of the selected year(s).

Best Regards,

Jayleny

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

[RiccardoRocchi]

Hi, Thanks for the answer, but in this way I can create the relationship between Date[Calendar] and one of the date of the main table (Date Actual, Expected, Planned) and the stacked chart works only for one measure. I need to see all the cumulative measures at the same time and to do this I probably have to work on the date (?) Thanks

[Ritaf1983]

Hi @RiccardoRocchi 

Can you please share a pbix or some dummy data that keep the raw data structure with expected results? It should help us clarify your scenario and test to coding formula.

How to Get Your Question Answered Quickly