Forum Discussion
%Change YoY (problem wth calculation)
Hi everyone,
in spite there ale quite a lot of videos and descriptions, I have not been able to get to the result I desire with comparing date across years. I have 1 data table with multiple sales in a month and 1 lookup date table. Relation between them is build.
The problem is if I compare 2018 and 2019 data, %change calculates with months in 2018 which have not occured in 2019 yet (makes it -100% these months).
This approach got me farthest but still the TOTALS are wrong.
1) Created ThisYear Measure = ThisYear = SUM([Sales] )
2) Created LastYear Measure = CALCULATE(Sales[ThisYear];SAMEPERIODLASTYEAR(Kalendar_Lookup[Datum].[Date]))
3) Created LasYear_cleared Measure=
4) Created %Change Measure = IF(OR(ISBLANK([LastYear_cleared]);ISBLANK([ThisYear]));BLANK();([ThisYear]/[LastYear_cleared]-1)) ... with the same aim as in Point 3)
The result is that Total shows -51,77% but it should show something like -20,2%. This miscalcuation of course shows in other views and tables for reps
Can you please advise what I am doing wrong. Thank you so much
2 Replies
- Greg_Deckler
Community Champion
See if my Time Intelligence the Hard Way provides a different way of accomplishing what you are going for.
https://community.powerbi.com/t5/Quick-Measures-Gallery/Time-Intelligence-quot-The-Hard-Way-quot-TITHW/m-p/434008
Greg_Deckler wrote:See if my Time Intelligence the Hard Way provides a different way of accomplishing what you are going for.
https://community.powerbi.com/t5/Quick-Measures-Gallery/Time-Intelligence-quot-The-Hard-Way-quot-TITHW/m-p/434008Thank you Greg. Gave me some time to figure it out but I managed to enter the Dax you suggest. I got somewhere with the ThisYear formula but the LastYear gives me no values, thus %change does not work either. Would you suggest what I am doing wrong?
1. I have got separate Date table (Kalendar_Lookup) with all dates available in the calendar and calucalted columns with Year and Month.
2. My data table includes Date (1st day of the month) + Value, there are multiple rows with the same date for multiple Sales reps. (normalized)
Another problem I see here is, that I will not recieve %Change 2017/2018 by this formulaThank you so much
M.
