Forum Discussion
Problems with Time Comparison
Hello everyone, I'm having a problem with time comparisons using the following formulas (indicated by a forum user):
YTDmeasure= TOTALYTD(SUM(Table[Total Sales]), Table[Dates])
PYTDmeasure= CALCULATE(YTDmeasure, PARALLELPERIOD(Table[Dates], -12, Months))
Comparison= DIVIDE(YTDmeasure-PYTDmeasure, PYTDmeasure)
(Obviously I insert the values of my table and I also added a comparison between this year and two years ago)
In general they work very well, but in some cases (like the one below) they report incorrect values:
CY = Current Year; PY = Previous Year; 2PY = comparison with two years ago; YTD = Year to Date
The following values, for example, are correct (I report them in order to have a further feedback):
N.b: all comparisons work up to today's date.
Thanks a lot to those who want to try to solve the problem and have a good day.
Gabriele
3 Replies
ElektroCED , Try like
Use date table
YTDmeasure= TOTALYTD(SUM(Table[Total Sales]), 'Date'[Dates])PYTDmeasure= TOTALYTD(SUM(Table[Total Sales]),dateadd( 'Date'[Dates],-1, year))
refer if needed
Power BI — Year on Year with or Without Time Intelligence
https://medium.com/@amitchandak.1978/power-bi-ytd-questions-time-intelligence-1-5-e3174b39f38a
https://www.youtube.com/watch?v=km41KfM_0uAI used the function that you suggested to me but when i try to filter for a state it goes in error.
Below the real functions I use:
IMPORTO_PAG_V1 YTDmeasure = TOTALYTD(SUM(view_PBI_ORD_CLI[IMPORTO_PAG_V1]), view_PBI_ORD_CLI[DATA_DOC])
IMPORTO_PAG_V1 PYTDmeasure = CALCULATE([IMPORTO_PAG_V1 YTDmeasure], PARALLELPERIOD(view_PBI_ORD_CLI[DATA_DOC], -12, MONTH))I can't understand why in general they are correct while when I select a filter (for example, the State) it gets confusingElektroCED , Please use date table, Which is very important for time intelligence
YTDmeasure= TOTALYTD(SUM(view_PBI_ORD_CLI[IMPORTO_PAG_V1]), 'Date'[Dates])
PYTDmeasure= TOTALYTD(SUM(view_PBI_ORD_CLI[IMPORTO_PAG_V1]),dateadd( 'Date'[Dates],-1, year))
refer
Why Time Intelligence Fails - Power bi 5 Savior Steps for TI: https://youtu.be/OBf0rjpp5Hw
Power BI — Year on Year with or Without Time Intelligence
https://medium.com/@amitchandak.1978/power-bi-ytd-questions-time-intelligence-1-5-e3174b39f38a
https://www.youtube.com/watch?v=km41KfM_0uA
