Year-to-Date Year-over-Year Measure Wrong at Year, Month, Quarter level

[Anonymous]

Hi Community,

I've scoured the internet for this but haven't been able to use any of the solutions posted.

I'm trying to do a YoY calculation that can be used by day, month, quarter, year, all YTD since I have new data coming in every day. I'm having an issue with calculating a year-over-year measure with a partial month. For some reason the 2020 YoY total and YoY% aggregation at the year, quarter, and month level are totally off. The day aggregation and YoY% is correct.

Total Tickets = DISTINCTCOUNT(table1[tkt_no])

Total Tickets - Previous Year YTD = CALCULATE([Total Tickets],SAMEPERIODLASTYEAR('Calendar (Date)'[Date]))

Total Tickets - Year-over-Year %Δ = ([Total Tickets]-[Total Tickets - Previous Year YTD])/[Total Tickets - Previous Year YTD]

Here is a picture of what I mean along with the DAX measures that I'm using.

Ideally, I would be using this in a KPI as well but it's completely off. 

[amitchandak]

You measures seem fine. Just check the calendar table is marked as date.

But these seem like this year vs last year measure. Looking at data not sure what is off. But Try datesytd or totalytd for YTD

Refer these example

YTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESYTD(('Date'[Date]),"12/31"))

Last YTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESYTD(dateadd('Date'[Date],-1,Year),"12/31"))
Last YTD complete Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESYTD(ENDOFYEAR(dateadd('Date'[Date],-1,Year)),"12/31"))

Last to last YTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESYTD(dateadd('Date'[Date],-2,Year),"12/31"))

Year behind Sales = CALCULATE(SUM(Sales[Sales Amount]),dateadd('Date'[Date],-1,Year))
2 Year behind Sales = CALCULATE(SUM(Sales[Sales Amount]),dateadd('Date'[Date],-2,Year))

Appreciate your Kudos. In case, this is the solution you are looking for, mark it as the Solution. In case it does not help, please provide additional information and mark me with @
Thanks. My Recent Blog -
Winner-Topper-on-Map-How-to-Color-States-on-a-Map-with-Winners , HR-Analytics-Active-Employee-Hire-and-Termination-trend
Power-BI-Working-with-Non-Standard-Time-Periods And Comparing-Data-Across-Date-Ranges

Connect on Linkedin

[Anonymous]

HI @amitchandak , the total lines for 2020, Quarter 1, and January are wrong. The total should not be 178,382, but 44,043 (sum of Jan 1 to Jan 8, 2019). What formulas should I use to ensure that the aggregation is correct at all those levels?

[amitchandak · Accepted solution]

Can you try year behind measure or put a filter to stop it in the max date you have

Total Tickets - Previous Year YTD =
var _max = maxx(table1,dateadd(table1[date],-1,year))
return CALCULATE([Total Tickets],SAMEPERIODLASTYEAR('Calendar (Date)'[Date]), 'Calendar (Date)'[Date] <=_max)

[Anonymous]

This did the trick, thank you @amitchandak !