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...
Anonymous's avatar
Anonymous
Not applicable
3 years ago

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.

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

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)