Compare Monthly average with Annual Average in same chart

[Anonymous]

Hi DAX Magicians,

I need help with a Power BI DAX formula wherein I want to display monthly average for each of my items - the month will be selected by user through a slicer. Against this average, the user also wants to see the annual average from  prior year for that item. I am using below dax measures. The current month is working fine but Prior Year annual average measure is giving incorrect values. Please see the below formulae and help rectify. This is my 1st post so apologies if i missed something. Thank you so much.

Current month avg 

Exception Rate % = DIVIDE(SUM(Table[Exceptions]),SUM(Table[Volume Examined]),0))

Prior Year Annual Avg =

PY Exception Rate % = CALCULATE(DIVIDE(SUM(Table[Exceptions]),SUM(Table[Volume Examined]),0),PREVIOUSYEAR('Date Table'[Date]))

[amitchandak · Accepted solution]

@Anonymous , This seems correct. Make sure the Date table is marked as date table, Option on right click of table.

Also, Date from the Table should not have timestamp

PY Exception Rate % = CALCULATE(DIVIDE(SUM(Table[Exceptions]),SUM(Table[Volume Examined]),0),PREVIOUSYEAR('Date Table'[Date]))

or try 

PY Exception Rate % = CALCULATE(DIVIDE(SUM(Table[Exceptions]),SUM(Table[Volume Examined]),0),datesytd(endofyear(dateadd('Date Table'[Date],-1,year))))

refer if needed Why Time Intelligence Fails - Power bi 5 Savior Steps for TI: https://youtu.be/OBf0rjpp5Hw

[Anonymous]

Many thanks for your help Amit. Much appreciated. It seems the issue was with PREVIOUSYEAR formula as my Financial Year ends in June but by default the PreviousYear takes December as end of FY. The below coding with "30/6" solved the issue. 

PREVIOUSYEAR('Date Table'[Date],"30/6")