How to calculate sales of next month, in the previous year.

[EVEAdmin]

Hi all,

From the table below, I'd like to calculate the total sales for the coming month, in the previous year, and display it on a card visual, as a single value.

So, for example, in May 2019, the card will display the total of June 2018
In June 2019, it will display the total of July 2018. And so on.

Is that possible, somehow? Thank you.

[MFelix]

Hi @EVEAdmin ,

You should create a similar measure to the one below:

PY month ahead = 
CALCULATE (
    TOTALMTD ( SUM ( Sales[Total] ); Sales[Invoice Date].[Date] );
    DATEADD ( Sales[Invoice Date].[Date]; -11; MONTH )
)

I would advice to create a calendar table that links to your invoice date and then create the following measure instead:

PY month ahead =
CALCULATE (
    TOTALMTD ( SUM ( Sales[Total] ); 'Calendar'[Date] );
    DATEADD ( 'Calendar'[Date]; -11; MONTH )
)

Regards,

MFelix

[EVEAdmin]

@MFelix many thanks

I went for option 2), but I must have done something wrong.

I created a Calendar table as below

Calendar = CALENDAR(MINX(Sales,Sales[InvoiceDate]),MAXX(Sales,Sales[InvoiceDate]))

and I linked it to the sales table as below

However, when I drag the measure to a card visual, it returns blank. 

[MFelix]

Hi @EVEAdmin ,

You need to take the bidirectionality between tables.

On the Relationship editor chose one to many and single on cross filter, One side will be the calendar and many the invoices table, should work as expected.

Regards,

MFelix

[EVEAdmin]

@MFelix 

I suspect there are issues with dates in the original data source. Will come back on this when I sort out the Calendar table

[parry2k · Accepted solution]

@EVEAdmin try following measure, and make it is best to make filter direction to single

Sum 11 = TOTALMTD( SUM( Sales1[Sales] ), DATEADD( c[Date],-11,MONTH))

[EVEAdmin]

@parry2k that worked

I first had to sort out my calendar table, as explained here, and then applied that measure. Thank you.

@MFelix for some reasons, your measure still returns blank. Will check it further as soon as I have more time, I'd like to get it to work, thank you.