topic Re: Rolling Moving Sum issue with Blank Values in DAX Commands and Tips

Rolling Moving Sum issue with Blank Values

KD13 — Wed, 24 May 2023 12:26:43 GMT

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

Re: Rolling Moving Sum issue with Blank Values

Greg_Deckler — Wed, 24 May 2023 13:56:02 GMT

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.

Re: Rolling Moving Sum issue with Blank Values

KD13 — Wed, 24 May 2023 14:08:58 GMT

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

Re: Rolling Moving Sum issue with Blank Values

tamerj1 — Wed, 24 May 2023 17:02:47 GMT

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

Re: Rolling Moving Sum issue with Blank Values

Anonymous — Wed, 24 May 2023 17:58:11 GMT

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.

Re: Rolling Moving Sum issue with Blank Values

Anonymous — Tue, 30 May 2023 09:22:27 GMT

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.

Re: Rolling Moving Sum issue with Blank Values

devanshi — Tue, 30 May 2023 11:43:54 GMT

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

RETURN

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

Re: Rolling Moving Sum issue with Blank Values

KD13 — Tue, 19 Sep 2023 16:16:46 GMT

THanks for your response.

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)