Cumulative based on two date fields and color formatting

[PBIuser0406]

Hi Folks,

There are 2 parts to my question

1) The file contains Reporting Month, Sale Month and Sale Amount. The requirement is to create Cumulative of Total Amount based on the Sale Month. Reporting Month columns indicates what is the closing for a particular. Anything amount in the past is considered as actuals and future months as forecast

Need help in creating a measure of cumulative Sales Amount as per Sale Month. Reporting month will be keep as a slicer/filter so that the user can understand the trend how it has changed for each month

The original data set contains dates of other years as well, however, my focus is only on 2025.

2) I would like to automate the color of different bars based on actuals / forecast. E.g.: If the user selects Reporting month Feb-2025, Jan & Feb (Actuals) will have the same color and from March a different one.

Thank you for your patience, any help is greatly appreciated

-RK

[PBIuser0406]

Hi @bhanu_gautam ,  I used the TotalYTD function and it worked as expected and the your suggestion of creating a column for Actuals and Estimates worked like a magic.

 Thank you so much .

[bhanu_gautam · Accepted solution]

@PBIuser0406 Create a new measure for the cumulative sales amount based on the Sale Month.

Cumulative Sales Amount =
CALCULATE(
SUM('Sales'[Sale Amount]),
FILTER(
ALL('Sales'[Sale Month]),
'Sales'[Sale Month] <= MAX('Sales'[Sale Month])
)
)

For the Reporting Month to your report. This will allow users to filter the data based on the selected Reporting Month.

Part 2: Automating Bar Colors Based on Actuals/Forecast
Create a new column to determine if the Sale Month is an actual or forecast based on the selected Reporting Month. You can use the following DAX formula:

Actual/Forecast =
IF(
'Sales'[Sale Month] <= MAX('Sales'[Reporting Month]),
"Actual",
"Forecast"
)

Assistant
To address your requirements, you can use Power BI to create the necessary measures and visualizations. Below are the steps to achieve both parts of your question:

Part 1: Creating a Cumulative Sales Amount Measure
Load your data into Power BI.
Create a new measure for the cumulative sales amount based on the Sale Month. You can use the following DAX formula:
DAX
Cumulative Sales Amount =
CALCULATE(
SUM('Sales'[Sale Amount]),
FILTER(
ALL('Sales'[Sale Month]),
'Sales'[Sale Month] <= MAX('Sales'[Sale Month])
)
)
This measure calculates the cumulative sum of the Sale Amount up to the current Sale Month.

Add a slicer for the Reporting Month to your report. This will allow users to filter the data based on the selected Reporting Month.
Part 2: Automating Bar Colors Based on Actuals/Forecast
Create a new column to determine if the Sale Month is an actual or forecast based on the selected Reporting Month. You can use the following DAX formula:
DAX
Actual/Forecast =
IF(
'Sales'[Sale Month] <= MAX('Sales'[Reporting Month]),
"Actual",
"Forecast"
)
Use conditional formatting in your bar chart to change the color based on the Actual/Forecast column. Here’s how you can do it:
Select your bar chart.
Go to the Format pane.
Expand the Data colors section.
Click on the fx button next to Default color.
In the Conditional formatting window, set the format style to Field value.
Select the Actual/Forecast column to base the colors on.

[PBIuser0406]

Hi.. Just to let you know that some date fields were blank in the dataset and I excluded them in Power Query and thinking that might be issue for getting the cumulative totals, but the problem still persists.

[PBIuser0406]

Thank you Bhanu for your time and providing your take on it. Before putting the post, I tried with a similar approach however was getting just the totals of the particular month rather than cumulative sum of entire year 2025. Here is the screenshot

Alternatively, I was thinking is there we can do Cumulative Total only for 2025 rather than the entire data. Please note that the data contains many other fields like Project Status, Phase, Code and some of are excluded as part of the filter.

Any suggestions welcome.