Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
daromel
Frequent Visitor

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

 

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!

 

 

6 REPLIES 6
vivran22
Community Champion
Community Champion

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/

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

 

 

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 - 

 

2020-02-13_1648.png

 

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.

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!

// 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))

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 )".

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors