here is my screenshot as you can see there is year wise sales figure. Current Year is 2020 and i want to calculate LYS Sale ( By dateadd function) i am getting different amount of sale in 2019. As per table value is 592397092.7700031 but when i am applied dateadd function to figure out the last year sale its giving the result of 1.49 bn.
@dipeshkumar30 Each row of the matrix has a filter over the year, that’s why DATEADD can traverse over dates, a card doesn’t generate any filter context.
PY sales =
CALCULATE (
[Total Sales],
DATEADD ( Dates[Date], -1, YEAR )
)
.
PY Sales 2 =
VAR LastSalesYear = YEAR ( CALCULATE ( MAX ( Sales[Order Date] ), ALL ( Sales ) ) )
VAR Result =
CALCULATE (
[Total Sales],
Dates[Calendar Year Number] = LastSalesYear - 1
)
RETURN
Result
Yes i agreed with you but why my card visual or measure considered last year value as 2013 and 2014. i just want to figure out last year sale means 2014 but it sumup last two years.
i used PY SALE = CALCULATE([TOTAL SALE],DATEADD(Dates[Date],-1,YEAR))
@dipeshkumar30 Not sure why it is summing both years, maybe you have some filters active?, try to create this in a fresh page and check if there are any report level filters, also, would need to look into the PBI file for the reason.
as i also thought before that may be some filter is applied so i tried with a small fresh excel datasource but the results same. In this case PY sale also miss figured even no filter is applied. Btw thanks for the support i have to check it once again and waiting for another solution.
@dipeshkumar30 Can you please share the file? Others would also want to see the file after our discussion.
@dipeshkumar30 Your date table doesn’t have an extra year to carry forward the calculation of the current year and date add works on date basis and has to shift every date by -1 year. That’s why you are getting the total for only first 2 years.
Solution: Add another year in your date table to include the current year in the grand total:
Dipesh PBI.pbix (89.8 KB)
thanks a lot sir
@dipeshkumar30 Hey Mate :
Instead of this use this one
PY SALE = CALCULATE([TOTAL SALE],SAMEPERIODLASTYEAR(Dates[Date])
This will work for you .
its not working i have tried the both dateadd and sameperiodlastyear but none of them is working. both calculation shows the same figure.
Did you try what I suggested earlier?
Hi @dipeshkumar30, we’ve noticed that no response has been received from you since the 9th of November. We just want to check if you still need further help with this post? In case there won’t be any activity on it in the next few days, we’ll be tagging this post as Solved. If you have a follow question or concern related to this topic, please remove the Solution tag first by clicking the three dots beside Reply and then untick the checkbox. Thanks!