Last months grow based on date slicer

[Pedrito]

Hi,

I have a table with sales and date and a calendar table. I want to selecte a month from a slicer and present a matrix with last month, last 3 months, last 6 months, last 9 months and last 12 months grow (for exemple, for 3 months would compare the sales sum of the month selected in the slicer and the 2 previous and the same months of the previous year), This is for several years and to be updated with the coming sales.

I was able to do it for month grow, but not for the others (formulas here):

Month = sum('Venta19-20'[Sales qty.])

Month -12 = (CALCULATE(sum('Venta19-20'[Sales qty.]);SAMEPERIODLASTYEAR(Calendario[[Fecha])))

Grow Month = ([Month]-[Month -12])/[Month -12]

Any idea?

Thank you

[v-jayw-msft · Accepted solution]

Hi @Pedrito ,

Check the measures as below.

this month = CALCULATE(SUM('Table'[value]),FILTER('Table','Table'[date].[Year]=SELECTEDVALUE('CALENDAR'[Date].[Year])&&'Table'[date].[MonthNo]=SELECTEDVALUE('CALENDAR'[Date].[MonthNo])))

this month previous year = CALCULATE(SUM('Table'[value]),FILTER('Table','Table'[date].[Year]=SELECTEDVALUE('CALENDAR'[Date].[Year])-1&&'Table'[date].[MonthNo]=SELECTEDVALUE('CALENDAR'[Date].[MonthNo])))

last 3 month = CALCULATE(SUM('Table'[value]),FILTER('Table','Table'[date].[Year]=SELECTEDVALUE('CALENDAR'[Date].[Year])&&'Table'[date].[MonthNo]<=SELECTEDVALUE('CALENDAR'[Date].[MonthNo])&&'Table'[date].[MonthNo]>SELECTEDVALUE('CALENDAR'[Date].[MonthNo])-3))

last 3 month previous year = CALCULATE(SUM('Table'[value]),FILTER('Table','Table'[date].[Year]=SELECTEDVALUE('CALENDAR'[Date].[Year])-1&&'Table'[date].[MonthNo]<=SELECTEDVALUE('CALENDAR'[Date].[MonthNo])&&'Table'[date].[MonthNo]>SELECTEDVALUE('CALENDAR'[Date].[MonthNo])-3))

last 6 month = CALCULATE(SUM('Table'[value]),FILTER('Table','Table'[date].[Year]=SELECTEDVALUE('CALENDAR'[Date].[Year])&&'Table'[date].[MonthNo]<=SELECTEDVALUE('CALENDAR'[Date].[MonthNo])&&'Table'[date].[MonthNo]>SELECTEDVALUE('CALENDAR'[Date].[MonthNo])-6))

last 6 month previous year = CALCULATE(SUM('Table'[value]),FILTER('Table','Table'[date].[Year]=SELECTEDVALUE('CALENDAR'[Date].[Year])-1&&'Table'[date].[MonthNo]<=SELECTEDVALUE('CALENDAR'[Date].[MonthNo])&&'Table'[date].[MonthNo]>SELECTEDVALUE('CALENDAR'[Date].[MonthNo])-6))

measure 1 = ([this month]-[this month previous year])/[this month previous year]

Measure 2 = ([last 3 month]-[last 3 month previous year])/[last 3 month previous year]

Measure 3 = ([last 6 month]-[last 6 month previous year])/[last 3 month previous year]

Best Regards,

Jay

[Pedrito]

Hi found this way to calculate the last 6 months from the selected date:

6M = CALCULATE(sum('Venta19-20'[Sales qty.]); DATESBETWEEN(Calendario[Fecha]; FIRSTDATE(DATESINPERIOD(Calendario[Fecha];LASTDATE(Calendario[Fecha]);-6;MONTH));LASTDATE(Calendario[Fecha])))

And now how can I calculate the sales to the same period of the previous year?

Thank you

[sturlaws]

Hi @Pedrito 

here is how I would do it. 

First, in your calendar table, create a new column like this:

YearMonthNum=('calendar'[year]-2010)*12 + 'calendar'[month]

This column will give you year and month as consecutive numbers, so if you e.g. want to see the previous month, just subtract 1 from the current selection.

Now you can create the measure for the last 3 month like this:

3 last months =
VAR _currentMonth =
    CALCULATE ( MAX ( yearMonthNumber ) )
RETURN
    CALCULATE (
        SUM ( 'sales'[salesAmount] ),
        FILTER (
            ALL ( 'calendar' ),
            'calendar'[yearMonthNumber] >= _currentMonth - 2
                && 'calendar'[yearMonthNumber] <= _currentMonth
        )
    )

and for the last 3 months previous year:

3 last months previous year =
VAR _currentMonth =
    CALCULATE ( MAX ( yearMonthNumber ) ) - 12
RETURN
    CALCULATE (
        SUM ( 'sales'[salesAmount] ),
        FILTER (
            ALL ( 'calendar' ),
            'calendar'[yearMonthNumber] >= _currentMonth - 2
                && 'calendar'[yearMonthNumber] <= _currentMonth
        )
    )

Cheers,
Sturla

If this post helps, then please consider Accepting it as the solution. Kudos are nice too.