Sales for last known 12 months

[Anonymous]

Hi,

I would like to have a measure that can tell me the total amount of sales over the last known 12 months. I need it as a variable for a forecast formula I’m working on.

The table below shows the outcome I’d be looking for. The total amount of sales from January to December 2019 is 214, which is the number that should get returned for January 2020.

The amount of sales in March 2020 is 0 or blank, so in that case it should continue the last 12 months that do have sales data in them, which are the months of February 2019 to January 2020, and which means it should return 215. The same applies to all future months.

How to do that?

Year	Month	Sales	Sales last known 12 months
2019	Jan	13
2019	Feb	32
2019	Mar	41
2019	Apr	15
2019	May	16
2019	Jun	17
2019	Jul	18
2019	Aug	15
2019	Sep	12
2019	Oct	20
2019	Nov	10
2019	Dec	5
2020	Jan	14	214
2020	Feb	40	215
2020	Mar		215
2020	Apr		215
2020	May		215
2020	Jun		215

Thanks
Bas

[Icey]

Hi @Anonymous ,

As far as I understand, your result should be like this:

You can create your measures like so:

Sales of last year = 
VAR LastNotBlankDate =
    CALCULATE (
        LASTDATE ( 'Table'[Date] ),
        FILTER (
            ALLSELECTED ( 'Table' ),
            LASTNONBLANK ( 'Table'[Date], 'Table'[Sales] )
        )
    )
VAR MinDate =
    CALCULATE ( FIRSTDATE ( 'Table'[Date] ), ALLSELECTED ( 'Table' ) )
VAR Sales_of_last_year =
    IF (
        MAX ( 'Table'[Date] ) <= LastNotBlankDate,
        CALCULATE (
            SUM ( 'Table'[Sales] ),
            DATESINPERIOD ( 'Table'[Date], LASTDATE ( 'Table'[Date] ), -1, YEAR )
        ),
        CALCULATE (
            SUM ( 'Table'[Sales] ),
            DATESINPERIOD ( 'Table'[Date], LastNotBlankDate, -1, YEAR )
        )
    )
RETURN
    IF (
        DATEDIFF ( MinDate, MAX ( 'Table'[Date] ), MONTH ) + 1 < 12,
        BLANK (),
        Sales_of_last_year
    )

Sales last known 12 months = 
VAR MinDate =
    CALCULATE ( FIRSTDATE ( 'Table'[Date] ), ALLSELECTED ( 'Table' ) )
RETURN
    CALCULATE (
        [Sales of last year],
        FILTER (
            ALL ( 'Table' ),
            DATEADD ( 'Table'[Date], 1, MONTH ) <= MAX ( 'Table'[Date] )
                && DATEADD ( 'Table'[Date], 1, MONTH ) >= MinDate
        )
    )

Best Regards,

Icey

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Anonymous]

@kento Thank you for your help but this solution unfortunately doesnt work for me.

@Icey That does the trick! I do get to some different numbers. I think this is because I have a master date table (that goes up to 2021) that is connect to the date in the sales table (where the sales date equals the last day any sales were recorded). When should I refer to the master date and when to the sales date?

Massive thanks both!

[Icey · Accepted solution]

Hi @Anonymous ,

Try this:

Sales of last year 2 = 
VAR LastNotBlankDate =
    CALCULATE (
        LASTDATE ( 'Date'[Date] ),
        FILTER (
            ALLSELECTED ( 'Sales' ),
            LASTNONBLANK ( 'Sales'[Date], 'Sales'[Sales] )
        )
    )
VAR MinDate =
    CALCULATE ( FIRSTDATE ( 'Sales'[Date] ), ALLSELECTED ( 'Sales' ) )
VAR Sales_of_last_year =
    IF (
        MAX ( 'Date'[Date] ) <= LastNotBlankDate,
        CALCULATE (
            SUM ( 'Sales'[Sales] ),
            DATESINPERIOD ( 'Date'[Date], LASTDATE ( 'Date'[Date] ), -1, YEAR )
        ),
        CALCULATE (
            SUM ( 'Sales'[Sales] ),
            DATESINPERIOD ( 'Date'[Date], LastNotBlankDate, -1, YEAR )
        )
    )
VAR CurrentRowDate =
    MAX ( 'Date'[Date] )
VAR Sales_of_last_year_2 =
    IF (
        DATEDIFF ( MinDate, CurrentRowDate, MONTH ) + 1 < 12,
        BLANK (),
        Sales_of_last_year
    )
RETURN
    IF ( DAY ( CurrentRowDate ) = 1, Sales_of_last_year_2 )

Sales last known 12 months 2 = 
VAR MinDate =
    CALCULATE ( FIRSTDATE ( 'Sales'[Date] ), ALLSELECTED ( 'Sales' ) )
VAR CurrentRowDate =
    MAX ( 'Date'[Date] )
VAR Sales_last_known_12_months =
    CALCULATE (
        [Sales of last year 2],
        FILTER (
            ALL ( 'Sales' ),
            DATEADD ( 'Date'[Date], 1, MONTH ) <= MAX ( 'Date'[Date] )
                && DATEADD ( 'Date'[Date], 1, MONTH ) >= MinDate
        )
    )
RETURN
    IF ( DAY ( CurrentRowDate ) = 1, Sales_last_known_12_months )

Best Regards,

Icey

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Anonymous]

That does the trick, thank you very much @Icey !

[kentyler]

I worked out your example

I substituted a date column holding the first day of every month for your month and year columns. That allowed me to use PREVIOUSYEAR() as a filter on CALCULATE().
So the logic of the measure is

IF the sales amount for the month is not blank, return the sales amount

Otherwise, sum up the sales for all the months that fall into the previous year of the current month.
The ALL() filter in the calculate statement is to include all the months in the calculation, and not just the month of the current row.
I'm a personal Power BI trainer.