Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
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!
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 -
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 )".
User | Count |
---|---|
47 | |
26 | |
21 | |
17 | |
15 |
User | Count |
---|---|
53 | |
34 | |
17 | |
17 | |
15 |