cancel
Showing results for
Did you mean:
Frequent Visitor

## Rolling Moving Sum issue with Blank Values

Hi All,

i'm trying to calculate the cumulative sales of last 6 months by product but i'm facing a challenge with the months with no data.

for example :

in the below table, eventhpugh the 1st product has no sales in March 23 , March 23 value should reflect : March + feb+jan+dec+nov+oct) but instead of this it's showing blank.

this is the formula that i'm using and i can't find a way to force the calculation even when some of the months are blank :

Last 6 months of sales =CALCULATE(SUM(Sales[Value]),DATEADD('00 -Calendar'[Date].[Date],-6,MONTH))

7 REPLIES 7
Helper V

pervious month sales =
VAR maxdate = MAX(Date tablename)

RETURN

CALCULATE(SUM(Sales),DATESINPERIOD(Date tablename,'Date'[date],-6,MONTH),'Date'[date]<=maxdate)

Community Support

Hi @KD13 ,

I suggest you to try code as below to create a measure.

``````Last 6 months of sales =
CALCULATE (
SUM ( Sales[Value] ),
FILTER (
ALL ( '00 -Calendar' ),
'00 -Calendar'[Date] <= MAX ( '00 -Calendar'[Date] )
&& '00 -Calendar'[Date]
>= EOMONTH ( MAX ( '00 -Calendar'[Date] ), -6 ) + 1
)
)``````

My Sample:

``00 -Calendar = ADDCOLUMNS(CALENDARAUTO(),"YearMonth",YEAR([Date])*100+MONTH([Date]),"MMM YY",FORMAT([Date],"MMM YY"))``

Result is as below.

Best Regards,
Rico Zhou

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

Frequent Visitor

the only issue with your solution is that eventhough the last month of sales is March 2023 the last 6 monts of sales calculation goes until August 2023 while it mus end at last real month of sales (March 2023)

Helper I

@KD13

I have recently solved a similar issue
Mine was based on years instead.

Solution I used:
1. Created a new table containing all years
2. Connected this table to the main one with a join on the year
3. Created a Running total from PBI quick measures using the Years column in the new table (so Base Value= [Value you want to show cumulative/running total] & Field = [Years from new column])
4. Make sure to place your Years/Months column from the NEW table on the chart or table you want to show
worked like a charm!

Edit: PowerBI will show blank if there are no sales/profit/value in a certain time period, which is very frustrating thinking that logically, it should carry the previous value as you stated in your post.

Super User

Hi @KD13

I don't use time intelligence functions in my code. They don't seem intelligent to me but seems that this not the case for you. Going along with that I guess DATEADD is not the correct function to use in this case rather you can use DATESINPERIOD like

=
CALCULATE (
SUM ( Sales[Value] ),
DATESINPERIOD ( '00 -Calendar'[Date], MAX ( '00 -Calendar'[Date] ), -6, MONTH )
)

Super User

@KD13 Hard to say exactly what is going on with the information provided but this may help, you would just use SUMX instead of AVERAGEX.

Become an expert!: Enterprise DNA
External Tools: MSHGQM
Latest book!:
Mastering Power BI 2nd Edition

DAX is easy, CALCULATE makes DAX hard...
Frequent Visitor

Hello,

thanks for your response but the issue is not solved with the proposed method.

the main challenge is that the Cumulative calculation stops wheneve ther's blank value.

Please see the below example :

in the row Sales by month we have the monthly sales.

the the cumulative sales row (in Green) , this is how the Moving 3 months sales should be working.

in the red row, this is how the current dax is behaving. whenever there's a blank the calculation stops.

the dax i'm using is as follows :

Last 6 months of sales =CALCULATE(SUM(Sales[Value]),DATEADD('00 -Calendar'[Date].[Date],-6,MONTH))

Announcements

#### Power BI September 2023 Update

Take a look at the September 2023 Power BI update to learn more.

#### Learn Live: Event Series

Join Microsoft Reactor and learn from developers.

#### Exclusive opportunity for Women!

Join us for a free, hands-on Microsoft workshop led by women trainers for women where you will learn how to build a Dashboard in a Day!

#### Power Platform Conference-Power BI and Fabric Sessions

Join us Oct 1 - 6 in Las Vegas for the Microsoft Power Platform Conference.

Top Solution Authors
Top Kudoed Authors