Forum Discussion

KD13's avatar
KD13
Icon for Helper I rankHelper I
3 years ago

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...
Greg_Deckler's avatar
Greg_Deckler
Icon for Community Champion rankCommunity Champion
3 years ago

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.

KD13's avatar
KD13
Icon for Helper I rankHelper I
3 years ago

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