Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

The Power BI DataViz World Championships are on! With four chances to enter, you could win a spot in the LIVE Grand Finale in Las Vegas. Show off your skills.

Reply
inikhilpv
Helper II
Helper II

How to plot rolling sum in column chart for everyday in a month

Hi,

I need to plot a column chart with rolling sum of sales. For everymonth it should show the rollingsum of sales till the end of the month. If there is no sales on a particular day, it should show the rolling sum till the previuos day.

I'm using the below DAX to find the rolling sum.

RollingSum =
CALCULATE(
SUM (Sales [sales],
FILTER(
ALLSELECTED('Date'),
'Date'[Date] <= MAX('Date'[Date])))

 

This is working fine. But when I apply the slicer (sales person id) it is not showing the date on the x axis if there is no sales transaction on that particular day as given below.

inikhilpv_2-1735650375153.png

 

day wise sales chart is given below.

inikhilpv_1-1735650317740.png

 

Please help me to have all the days displayed on the above chart evenif there is no transaction happend on those days.

 

2 REPLIES 2
bhanu_gautam
Super User
Super User

@inikhilpv , Try using 

 

Create a Date Table: Ensure you have a date table that includes all the dates in the range you are interested in. This table should not have any missing dates.

Create a Relationship: Make sure there is a relationship between your Sales table and the Date table.

Modify the DAX for Rolling Sum: Adjust your DAX formula to use the Date table.

 

RollingSum =
CALCULATE(
SUM(Sales[sales]),
FILTER(
ALLSELECTED('Date'),
'Date'[Date] <= MAX('Date'[Date])
)
)




Did I answer your question? Mark my post as a solution! And Kudos are appreciated

Proud to be a Super User!




LinkedIn






I did the same way. Created a date table and related to sales date in the sales table. Still I'm not able to plot the rolling total if there is no sales on a particular day.

Helpful resources

Announcements
Feb2025 Sticker Challenge

Join our Community Sticker Challenge 2025

If you love stickers, then you will definitely want to check out our Community Sticker Challenge!

Jan NL Carousel

Fabric Community Update - January 2025

Find out what's new and trending in the Fabric community.