calculate sum of sales of 12 month ago from the last date in data set

[daromel]

Hi,

I need help with a formula in DAX

I need to calculate the sum of sales of 12 month ago from the last date in data set, regardeless the context.

for exaple, now we are in February 2020 but i have data until January 2020, so I need the sum of sales of january 2019.

It would be something like this. But it dosen´t work:

=CALCULATE([sumSales];FILTER(SalesTable;SalesTable[Date]>=eofmonth(max(all(Table[Date]));-1)+1

in others words

=CALCULATE([sumSales];FILTER(SalesTable;SalesTable[Date]>= first day from the max month in dataset

Thanks!

[vivran22]

Hello @daromel 

If you need sales for 12 months ago then you can use the following measure:

Last Year Sales =
CALCULATE ( [Total Sales], DATEADD ( Orders[Start of Month], -12, MONTH ) )

-- where Orders[Start of Month] is the date column available in your data table

Hope this helps.

Regards,
Vivek

If it helps, please mark it as a solution

Kudos would be a cherry on the top 🙂

https://www.vivran.in/

[daromel]

Thank for the answaer, but I need the sum of sales from 12 month ago until the last sale.

[AlexAlberga727]

Measure 2 = 

VAR LorderDATE = MAX( fSales[InvoiceDate] )
VAR YPriorDATE = LorderDATE - 365
  
RETURN

CALCULATE( [Total Revenue], 
    DATESBETWEEN( dDateTable[Date] , YPriorDATE, LorderDATE ))

Create variable to identify those days as they will vary. Then run a Calculation on Total rev with the DATESBETWEEN DAX.

Let me know if this works! Works in my Model - 

If so, please throw me a thumbs up and mark as solution.

Thanks!

EDIT - Above I created a calculated Column which was just so you can see that the LOrderDATE and YPriorDATE is working correctly... However, I mistakenly added a year opposed to back back a year... Do note - The measure I linked above works correctly.

[daromel]

Hi Alex, yes it works. Thank you very much!

Just one more question, it is posible to use something similar to dateadd to calculate the YPriorDATE ?

Something like  YPriorDATE = dateadd(LorderDate,1, year)

Thanks again!

[AlexAlberga727]

// Calculated Column in fact table to gather INVOICEDATE LY = 

Date LY = DATEADD(fSales[InvoiceDate], -1, YEAR)

// Measures to calculate TODAY LY = 

Today LY = TODAY() - 365
Today LY v2 = CALCULATE( TODAY(), DATEADD( dDateTable[Date], -1, YEAR))

[AlexAlberga727]

Whenever I do time intelligent calculations I always use DATEADD. Very helpful, you're able to set different intervals. If you want to look behind or ahead X Days, month, quarters, or years - you can do so with DATEADD.

You can use as Vivran22 mentioned, or use:   "... , -1 , YEAR )".