Calculate Moving monthly Sales (acumulative) bring from previous year

[clarainesgg]

Hi, 

I need to calculate the acumulative sales starting from a certain period (something similiar to Sales YTD

I used this: 

Sales YTD =

IF(

    ISFILTERED('financials'[Date]),

    ERROR("Time intelligence quick measures can only be grouped or filtered by the Power BI-provided date hierarchy or primary date column."),

    TOTALYTD(SUM('financials'[ Sales]), 'Calendar'[Date])

)

However, i need to acumulate the sales from previous month:

Below is my mock up data.

Thanks,

Year	Quarter	Month	Sales	Acum Sales
2013	Qtr 4	November	6.350
2013	Qtr 4	December	60.000
2014	Qtr 1	January	50.000
2014	Qtr 1	February	70.000	6.350
2014	Qtr 1	March	45.000	66.350
2014	Qtr 2	April	25.000	116.350
2014	Qtr 2	May	30.000	186.350
2014	Qtr 2	June	45.000	231.350
2014	Qtr 3	July	10.000	256.350
2014	Qtr 3	August	50.000	286.350
			391.350

[Sergii24]

Hi @clarainesgg, here is an algorithm:

1. Create a ranking based on a necessary condition, so you can offset from "current period" to N previous "periods"
2. use Calculate() to overwrite the current filter context and calculate the formula over all rows previous to the offset (you can add an upper limit as well if needed)
3. Display a result on canvas

Attached you can find an example for your specific case when there is only one flat table and your numbers are available at monthly level only. Read it in the following way:

1. Calcualted column "Month Rank" is created to obtain a month number as number instead of text
2. Calcualted column "Year Month Number" is created to obtain a number for both month and year together (because month number is repeating over years)
3. Calculated column "RowNumberByYearMonthNumber" is created for the ranking
4. Create a measure "Accum Sales"

Acum Sales = 
VAR _RowNumberByYearMonthNumber = MIN( 'Table'[RowNumberByYearMonthNumber] ) //get current value of the column
VAR _Offset= 3      //when you want to start capturing previous data
VAR _CurrentOffset = _RowNumberByYearMonthNumber - _Offset      //to check if you arrived to a necessary month
VAR _Result =
    IF(
        _CurrentOffset >= 0,            //when we are at a necessary month or after
        CALCULATE(                      //than calculate a sum
            SUM( 'Table'[  Sales ] ),
            ALL( 'Table' ),             //necessary to remove filters from other columns such as year, month etc.
            'Table'[RowNumberByYearMonthNumber] <= _CurrentOffset
        ),
        BLANK()                         //otherwise keep it blank
    )

RETURN _Result

Here is the final result:

In case you have a proper data model with Calendar table marked as "Date table" (Do You Need a Date Dimension? - RADACAD) and sales at daily granularity you can follow @rajendraongole1's suggestion and use date column for the ranking system.

Good luck with your project! 🙂

[clarainesgg]

I got an error when i want to create a row_number

[rajendraongole1]

Hi @clarainesgg -can you check the below modified dax formaule

Cumulative Sales =
VAR CurrentDate = MAX('Calendar'[Date])
VAR FirstDateOfPreviousMonth = EDATE(CurrentDate, -1) // Shifts date back by one month
RETURN
CALCULATE(
SUM('financials'[Sales]),
FILTER(
ALL('Calendar'),
'Calendar'[Date] >= FirstDateOfPreviousMonth && 'Calendar'[Date] <= CurrentDate
)
)

I am assuming if the current context is March 2014, this DAX formula will accumulate sales starting from February 2014 and continue to accumulate as you move forward.Hope this works 

[clarainesgg]

I need to start from february to bring my sales from nov-23 and so on.